1) What are the different types of manufacturing processes? And what are the primary differences between them? OR Explain Process Manufacturing v/s Discrete manufacturing?
Discrete Manufacturing is the production of distinct items that use bills of material and routings to determine costs and lead times. Eg: Car manufacturing, Computer Manufacturing etc you get all the parts and assemble them.
OPM on the other hand caters to capabilities to allow for multiple units of
measure because the flexibility in batch production is required in process
industries. e.g Chemical Industries, food processing etc. This involves complex
internal processes and need a high level of control.
Eg: You mix x kgs of sugar and n litres of milk, you get a sweet as the output.
This can be in kilos, litres or smaller units. If you do not allow the water to
evaporate the output will be a liquid, if you heat longer the output will be a
burfi.
What is the difference between the two?
1)ODM uses BOM (Bill of Materials), OPM uses formulas and Recipes
2)UOM will vary depending on the material used in OPM but in ODM UOM will be
each or unit
3)ODM assembles or builds things, OPM mixes , blends and transforms or
converts.
4)OPM is driven by item attributes and ODM is driven by part number
Oracle Process Manufacturing –
It the entire product lifecycle for
recipe-based manufacturing, from new product development, recipe
management and production, to cost, quality,
and regulatory management. It enables you to formulate products
to individual customer specifications, manage variability, optimize
capacity, and drive continuous process improvement
2) What are the modules that come under
oracle’s OPM solution?
Answer: OPM includes OPM Process Planning,
Product Development(which includes Formula,Recipe,Quality), Production,
Financials(Costing,MAC), Logistics, Regulatory Management etc. These are
products which come under this umbrella..
· OPM Cost
Management
· OPM Formula
Management
· OPM Intelligence
· OPM Inventory
Management
· OPM Laboratory
Management
· OPM Master
Production Scheduling
· OPM Material
Requirements Planning
· OPM Production
Management
· OPM Purchasing
Management
· OPM Quality
Management
· OPM Capacity
· OPM Sales
Management
· Oracle Financial
3) Name and explain
frequently used terms in Oracle Process Manufacturing?
Answer:
4) Name some important
tables used in OPM? ORWhich tables stores the formula information?
Answer:
1) select
a.FORMULA_ID,a.formula_no,a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,b.organization_id,decode(b.line_type,-1,’Ingredient’,’Product’)
Type
from FM_FORM_MST a,FM_MATL_DTL
b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS<>’COSTING’
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID
from FM_FORM_MST a,FM_MATL_DTL
b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS<>’COSTING’
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID
from FM_FORM_MST a,FM_MATL_DTL
b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS<>’COSTING’
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID
2) Select
b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,decode(c.line_type,-1,’Ingredient’,’Product’)
Type,
sum(e.TRANSACTION_QUANTITY) quantity
from apps.GME_BATCH_HEADER a,apps.gmd_recipes
b,gmd_recipe_validity_rules grr,apps.gme_material_details
c,apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and
a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from
apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and
:to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between
:from_date and :to_date
group by
b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION
sum(e.TRANSACTION_QUANTITY) quantity
from apps.GME_BATCH_HEADER a,apps.gmd_recipes
b,gmd_recipe_validity_rules grr,apps.gme_material_details
c,apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and
a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from
apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between
:from_date and :to_date
group by
b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION
sum(e.TRANSACTION_QUANTITY) quantity
from apps.GME_BATCH_HEADER a,apps.gmd_recipes
b,gmd_recipe_validity_rules grr,apps.gme_material_details
c,apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from
apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and
:to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between
:from_date and :to_date
group by
b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION
5) Explain what do you mean by
Formula and Recipe?
Formula is Ingredients and their proportions
Receipe is Formula + Routing.
6) What are different kinds of losses?
Fixed loss and Variable loss.
Q. Once an item is assigned to an
organization, is it possible to remove this association at a later time?
A. If you have assigned an item to an
organization, it can only be deleted from that organization if the item has NOT
been transacted. If it has been transacted against you cannot delete the item
from the organization. You can, however, make the status of the item “inactive”
on the main region of the Item Define form INVIDITM in ‘Item Status’ field.
This will prevent users from transacting against the item although the item
will continue to be included in LOV’s.
Q. How do you setup Unit of Measure for an
item?
A. When transacting an item, it will always
default to the defined Primary Unit of Measure, so if you need to use another
UOM, you must select it from the List of Values when you transact it. This
implies that you want the Primary Unit of Measure to be that which is used most
often and which is likely to be the smallest unit of that type of item. Once
you have set the primary UOM for an item, it CANNOT be changed.
Q. How do I change an item’s locator control
method once I have transacted against it, for example from No Control to
Prespecified?
A. You cannot change this attribute if there
is onhand quantity for the item. You must first perform a miscellaneous issue
of all quantity. Then the attribute can be changed and once the locator(s) are
set up, you can perform a miscellaneous receipt to receive the quantity back
into a specified subinventory/locator.
Q. How do I change an item’s Unit of Measure?
A. You CANNOT change the UOM for an item once
the UOM is assigned to the item. The suggested solution is as follows: If the
item has not been transacted you can delete the item and then re-add it with
the correct UOM. If the item has been transacted and has no outstanding orders,
purchase orders or WIP jobs you can do the following if approved by your management.
1. Rename the existing item ’123′ to ’123-Bad
UOM’ and save the item. Any prior history for item ’123′ will now be associated
with item ’123-Bad UOM’.
2. Add a new item ’123′ with the correct UOM.
Q. How do I delete an Item Template I no
longer use?
A. Suggested steps to follow to delete
unwanted Item Templates:
1. Navigate Setup: Items: Templates – Find
Item Templates form appears.
2. Click on the LOV, and select the Item
Template that is to be removed.
3. Click the [Find] button on the Find Item
Templates form. The Item Templates Summary form appears, with the Item Template
selected.
4. Click the [Open] button on the Item
Templates Summary form, and the Item Templates form appears.
5. From the menu, select Edit | Delete Record,
then select Action | Save and Proceed. 6. Close the Item Templates Summary
form. Be careful not to delete Item Templates you may need again as they are
not retrievable once deleted.
Q. Is there a way to create custom item
attributes?
A. No but you can create and define the
descriptive flexfield for this form. This will allows you to associate
additional data with an item such as an attribute. See the Oracle System
Administrators
Users Guide and the Oracle Flexfields Manual
for information on creating Descriptive Flexfields.
Q. Why does the application prompt you for
Organization when you enter the Master Item form for the very first time even
though you have only one Master Organization.
A. Once you setup a Multi-Org environment and
you enter the form for the first time, there is the possibility there can be
more than one Master Organization. The application
therefore prompts you for an organization
which is then used to determine the Master Organization associated with it. In
other words, you are choosing the Master Organization by choosing one of the
Orgs that belongs to it.
Q. How do you change the control level for
item attributes from master level to organization level for example. When I try
to change this in the Attribute Control Form, I get the message: frm-40200:
field protected against update.
A. First change value of the field called
“status” to “not used”. You should then be able to change the control level of
the attribute from master level to org level. Make sure and change to status
back to what it was before the change and save the record
Q. How do I inactivate an item?
A. On the Master Item Form
(Inventory->Items->Master Items), change the item status field to
“Inactive” or any another defined status which disables all attributes that
make an item usable (stockable, shippable, orderable, etc.). The “Inactivate”
status comes pre-seeded with the Application and is for this purpose.
Q. What characters can I use in an item name
(including special characters)?
A. The only hard fast rule is not to use the
character designated in the segment delimiter for the System Item Flexfield.
Also, you will not be able to enter any characters not allowed by the valueset
associate with the flexfield. It is recommended that you avoid the use of any
char that can be used as a delimiter to avoid future problems. It is best to
not use special characters like #@%& if at all possible as these may have a
negative impact down the road. Some characters, like percent (%), underscore
(_), and pound(#), have special meaning when performing queries by form as do
the query operator characters (=!><).
Q. Can you change the costing enabled and
inventory asset flags on an item that has already been transacted against even
if there is no on-hand quantities for the item?
A. Yes, you can change the flags. If you are
changing the flags from checked to unchecked you will receive a warning that
the associated cost records will be deleted alerting you that the Cost of Goods
will not be calculated any longer for transactions of this item. In effect what
you are doing is changing the item from an asset item to an expense item. If
you are changing the flags from being unchecked to checked then a database
trigger will fire and cause cost records to be
added for this item. There is no warning that is given.
Q. When I create a new item, and choose to
assign to All orgs, the application allows me to even though some of those
organizations are restricted to my responsibility. Is this the way it is
supposed to work? If I assign in an Organization by Organization fashion, I am
not allowed to assign to the restricted organizations.
A. The situation described is standard
functionality. Even if a responsibility has limited access to a certain number
of organizations, the “Assign to All Organizations” check box will assign the
item to all orgs, not just the ones limited to the user’s responsibility.
Q. How do you tell that an item is an expense
item or asset item?
A. Navigate to the Organization Items form:
Inventory->Items->Organization Items. Query up the item in question.
Change to the “Costing” alternative region. The value of the “Inventory Asset
Value” flag determines this. If checked, the item is an asset item.
Q. Is there a way to delete or disable an item
revision once entered?
A. There is no way to delete a revision nor is
there a way to disable it. It must remain there for historical data. The
suggested workaround is to create a new item, transfer any Quantity on-hand
from the old item to new item and delete the old item. The only down side is you
will lose some transaction history
Share this:
·
Twitter
·
Facebook
·
APRIL
22, 2015/BY ORACLE ERP APPS GUIDE
APPS TECHNICAL, BASIC CONCEPTS, OPM, SCRIPTS, SQL
OPM ACCOUNTING PREPROCESSOR
DEBUGGING SCRIPTS IN R12
Orgn: &&organization_id
==================
SELECT * FROM mtl_parameters WHERE organization_id =
&&organization_id;
Item details FOR Item: &&inv_item_id
=========================
SELECT *
FROM mtl_system_items_kfv
WHERE organization_id =
&&organization_id
AND inventory_item_id =
&&inv_item_id;
Cost Type details FOR Cost Type: &&cost_type
=================================
SELECT * FROM cm_mthd_mst WHERE cost_mthd_code =
‘&&cost_type’;
Period Balances FOR Item: &&inv_item_id Orgn:
&&organization_id
==============================================
SELECT pbal.inventory_item_id AS item_id,
pbal.primary_quantity AS quantity,
pbal.organization_id AS
mtl_organization_id,
pbal.period_balance_id AS perd_bal_id,
NVL(pbal.lot_number,’ ‘),
pbal.subinventory_code,
pbal.locator_id,
pbal.costed_flag,
pbal.acct_period_id
FROM org_acct_periods oap,
gmf_period_balances pbal,
gmf_fiscal_policies gfp,
gl_ledgers gl,
hr_organization_information hoi
WHERE oap.period_start_date >= TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND oap.schedule_close_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND hoi.organization_id =
&&organization_id
AND hoi.org_information_context = ‘Accounting Information’
AND gfp.legal_entity_id =
hoi.org_information2
AND gl.ledger_id
= gfp.ledger_id
AND oap.period_set_name =
gl.period_set_name
AND oap.organization_id =
pbal.organization_id
AND pbal.acct_period_id =
oap.acct_period_id
AND pbal.inventory_item_id =
&&inv_item_id
AND pbal.organization_id =
&&organization_id ;
Item Costs
==========
SELECT c.inventory_item_id ,
c.organization_id ,
c.period_id ,
TO_CHAR (end_date, ‘MM/DD/YYYY hh24:mi:ss’) perd_end_date ,
m.cost_mthd_code ,
c.cost_type_id ,
c.acctg_cost ,
c.itemcost_id ,
d.cost_cmpntcls_id ,
d.cost_analysis_code ,
d.cmptcost_amt
FROM gl_item_cst c,
gl_item_dtl d,
cm_mthd_mst m
WHERE d.itemcost_id = c.itemcost_id
AND c.inventory_item_id =
&&inv_item_id
AND c.organization_id =
&&organization_id
AND c.cost_type_id = m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
ORDER BY c.end_date DESC ;
Actual Cost Adjs
============
SELECT d.inventory_item_id,
d.organization_id,
d.cost_cmpntcls_id,
d.cost_analysis_code,
d.cost_adjust_id,
d.adjust_qty,
d.adjust_qty_uom,
d.adjust_cost,
d.reason_code,
NVL(d.adjustment_ind, DECODE(d.adjust_qty, 0, 1, 0))
adjustment_ind,
TO_CHAR(d.adjustment_date,’MM/DD/YYYY hh24:mi:ss’),
cmpt.usage_ind,
d.adjust_status,
d.subledger_ind,
d.delete_mark
FROM cm_adjs_dtl d,
cm_cmpt_mst cmpt,
cm_mthd_mst m
WHERE d.inventory_item_id =
&&inv_item_id
AND d.organization_id =
&&organization_id
AND cmpt.cost_cmpntcls_id = d.cost_cmpntcls_id
AND d.cost_type_id = m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
AND d.adjustment_date >=
TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND d.adjustment_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
ORDER BY d.inventory_item_id,
d.organization_id,
d.adjustment_date,
d.cost_adjust_id ;
Lot Cost Adjs
==========
SELECT lca.inventory_item_id,
lca.lot_number,
lca.reason_code,
TO_CHAR(lca.adjustment_date,’MM/DD/YYYY hh24:mi:ss’)
adjustment_date,
lca.adjustment_id,
lcad.cost_cmpntcls_id,
lcad.cost_analysis_code,
(NVL(lcad.adjustment_cost,0) * NVL(lca.onhand_qty,0))
delta_amount,
lca.onhand_qty,
lca.organization_id,
lcad.adjustment_cost,
cmpt.usage_ind ,
lca.old_cost_header_id ,
lca.new_cost_header_id ,
lca.gl_posted_ind ,
lca.applied_ind
FROM gmf_lot_cost_adjustment_dtls lcad,
gmf_lot_cost_adjustments lca,
cm_cmpt_mst cmpt,
cm_mthd_mst m
WHERE lca.cost_type_id = m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
AND lca.adjustment_date >= TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND lca.adjustment_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND lcad.adjustment_id = lca.adjustment_id
AND cmpt.cost_cmpntcls_id = lcad.cost_cmpntcls_id
AND lca.inventory_item_id =
&&inv_item_id
AND lca.organization_id =
&&organization_id
ORDER BY 1,
12,
3,
4,
6,
7,
8 ;
GL Expense Allocation
===============
SELECT gps.calendar_code,
gps.period_code,
gps.cost_type_id,
‘ ‘ dtl_cost_mthd_code,
bas.inventory_item_id AS item_id,
bas.whse_code,
bas.cmpntcls_id,
cmpt.cost_cmpntcls_code,
bas.analysis_code,
dtl.allocated_expense_amt,
NVL(dtl.period_qty,0),
bas.organization_id,
dtl.period_id,
dtl.alloc_id,
dtl.line_no,
dtl.allocdtl_id,
cmpt.usage_ind ,
mst.alloc_code ,
dtl.ac_status ,
dtl.delete_mark
FROM gl_aloc_dtl dtl,
gl_aloc_bas bas,
gl_aloc_mst mst,
gmf_period_statuses gps,
cm_cmpt_mst cmpt ,
hr_organization_information hoi ,
cm_mthd_mst m
WHERE mst.legal_entity_id = hoi.org_information2
AND hoi.organization_id =
bas.organization_id
AND hoi.org_information_context = ‘Accounting Information’
AND bas.inventory_item_id =
&&inv_item_id
AND bas.organization_id =
&&organization_id
AND cmpt.cost_cmpntcls_id = bas.cmpntcls_id
AND dtl.alloc_id = mst.alloc_id
AND dtl.alloc_id = bas.alloc_id
AND dtl.line_no = bas.line_no
AND dtl.cost_type_id = gps.cost_type_id
AND gps.period_id = dtl.period_id
AND gps.start_date >= TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND gps.end_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND gps.legal_entity_id= hoi.org_information2
AND gps.cost_type_id = m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
AND gps.delete_mark = 0
ORDER BY dtl.period_id,
dtl.cost_type_id,
bas.inventory_item_id,
bas.organization_id,
bas.cmpntcls_id,
bas.analysis_code ;
Extract Header (COSTREVAL)
====================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code = ‘REVALUATION’
AND geh.event_type_code = ‘COSTREVAL’
AND geh.transaction_id IN
(SELECT pbal.period_balance_id
FROM org_acct_periods oap,
gmf_period_balances pbal,
gmf_fiscal_policies gfp,
gl_ledgers gl,
hr_organization_information hoi
WHERE oap.period_start_date >=
TO_DATE(‘&&datefrom’,’MM/DD/YYYY’)
AND oap.schedule_close_date <=
TO_DATE(‘&&dateto’,’MM/DD/YYYY’)
AND hoi.organization_id =
&&organization_id
AND hoi.org_information_context = ‘Accounting Information’
AND gfp.legal_entity_id =
hoi.org_information2
AND gl.ledger_id
= gfp.ledger_id
AND oap.period_set_name =
gl.period_set_name
AND oap.organization_id =
pbal.organization_id
AND pbal.acct_period_id =
oap.acct_period_id
AND pbal.inventory_item_id =
&&inv_item_id
AND pbal.organization_id =
&&organization_id
);
Extract Lines (COSTREVAL)
==================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
gmf.gmf_xla_extract_lines gel
WHERE gel.header_id = geh.header_id
AND geh.entity_code = ‘REVALUATION’
AND geh.event_type_code = ‘COSTREVAL’
AND geh.transaction_id IN
(SELECT pbal.period_balance_id
FROM org_acct_periods oap,
gmf_period_balances pbal,
gmf_fiscal_policies gfp,
gl_ledgers gl,
hr_organization_information hoi
WHERE oap.period_start_date >=
TO_DATE(‘&&datefrom’,’MM/DD/YYYY’)
AND oap.schedule_close_date <=
TO_DATE(‘&&dateto’,’MM/DD/YYYY’)
AND hoi.organization_id =
&&organization_id
AND hoi.org_information_context = ‘Accounting Information’
AND gfp.legal_entity_id =
hoi.org_information2
AND gl.ledger_id
= gfp.ledger_id
AND oap.period_set_name =
gl.period_set_name
AND oap.organization_id =
pbal.organization_id
AND pbal.acct_period_id =
oap.acct_period_id
AND pbal.inventory_item_id =
&&inv_item_id
AND pbal.organization_id =
&&organization_id
);
SLA Events (COSTREVAL)
=================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
xla.xla_events xe
WHERE xe.event_id = geh.event_id
AND geh.entity_code = ‘REVALUATION’
AND geh.event_type_code = ‘COSTREVAL’
AND geh.transaction_id IN
(SELECT pbal.period_balance_id
FROM org_acct_periods oap,
gmf_period_balances pbal,
gmf_fiscal_policies gfp,
gl_ledgers gl,
hr_organization_information hoi
WHERE oap.period_start_date >=
TO_DATE(‘&&datefrom’,’MM/DD/YYYY’)
AND oap.schedule_close_date <=
TO_DATE(‘&&dateto’,’MM/DD/YYYY’)
AND hoi.organization_id =
&&organization_id
AND hoi.org_information_context = ‘Accounting Information’
AND gfp.legal_entity_id =
hoi.org_information2
AND gl.ledger_id
= gfp.ledger_id
AND oap.period_set_name =
gl.period_set_name
AND oap.organization_id =
pbal.organization_id
AND pbal.acct_period_id =
oap.acct_period_id
AND pbal.inventory_item_id =
&&inv_item_id
AND pbal.organization_id =
&&organization_id
);
Extract Header (ACTCOSTADJ)
=======================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code = ‘REVALUATION’
AND geh.event_type_code = ‘ACTCOSTADJ’
AND geh.transaction_id IN
(SELECT d.cost_adjust_id
FROM cm_adjs_dtl d,
cm_cmpt_mst cmpt,
cm_mthd_mst m
WHERE d.inventory_item_id =
&&inv_item_id
AND d.organization_id =
&&organization_id
AND cmpt.cost_cmpntcls_id = d.cost_cmpntcls_id
AND d.cost_type_id =
m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
AND d.adjustment_date >=
TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND d.adjustment_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
);
Extract Lines (ACTCOSTADJ)
===================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
gmf.gmf_xla_extract_lines gel
WHERE gel.header_id = geh.header_id
AND geh.entity_code = ‘REVALUATION’
AND geh.event_type_code = ‘ACTCOSTADJ’
AND geh.transaction_id IN
(SELECT d.cost_adjust_id
FROM cm_adjs_dtl d,
cm_cmpt_mst cmpt,
cm_mthd_mst m
WHERE d.inventory_item_id =
&&inv_item_id
AND d.organization_id =
&&organization_id
AND cmpt.cost_cmpntcls_id = d.cost_cmpntcls_id
AND d.cost_type_id =
m.cost_type_id
AND m.cost_mthd_code =
‘&&cost_type’
AND d.adjustment_date >=
TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND d.adjustment_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
);
Sla Events (ACTCOSTADJ)
====================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
xla.xla_events xe
WHERE xe.event_id = geh.event_id
AND geh.entity_code = ‘REVALUATION’
AND geh.event_type_code = ‘ACTCOSTADJ’
AND geh.transaction_id IN
(SELECT d.cost_adjust_id
FROM cm_adjs_dtl d,
cm_cmpt_mst cmpt,
cm_mthd_mst m
WHERE d.inventory_item_id =
&&inv_item_id
AND d.organization_id =
&&organization_id
AND cmpt.cost_cmpntcls_id = d.cost_cmpntcls_id
AND d.cost_type_id =
m.cost_type_id
AND m.cost_mthd_code =
‘&&cost_type’
AND d.adjustment_date >=
TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND d.adjustment_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
);
Extract Header (LOTCOSTADJ)
=====================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code = ‘REVALUATION’
AND geh.event_type_code = ‘LOTCOSTADJ’
AND geh.transaction_id IN
(SELECT lca.adjustment_id
FROM gmf_lot_cost_adjustment_dtls lcad,
gmf_lot_cost_adjustments lca,
cm_cmpt_mst cmpt,
cm_mthd_mst m
WHERE lca.cost_type_id = m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
AND lca.adjustment_date >= TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND lca.adjustment_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND lcad.adjustment_id = lca.adjustment_id
AND cmpt.cost_cmpntcls_id = lcad.cost_cmpntcls_id
AND lca.inventory_item_id =
&&inv_item_id
AND lca.organization_id =
&&organization_id
);
Extract Lines (LOTCOSTADJ)
===================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
gmf.gmf_xla_extract_lines gel
WHERE gel.header_id = geh.header_id
AND geh.entity_code = ‘REVALUATION’
AND geh.event_type_code = ‘LOTCOSTADJ’
AND geh.transaction_id IN
(SELECT lca.adjustment_id
FROM gmf_lot_cost_adjustment_dtls lcad,
gmf_lot_cost_adjustments lca,
cm_cmpt_mst cmpt,
cm_mthd_mst m
WHERE lca.cost_type_id = m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
AND lca.adjustment_date >= TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND lca.adjustment_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND lcad.adjustment_id = lca.adjustment_id
AND cmpt.cost_cmpntcls_id = lcad.cost_cmpntcls_id
AND lca.inventory_item_id =
&&inv_item_id
AND lca.organization_id =
&&organization_id
);
SLA Events (LOTCOSTADJ)
==================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
xla.xla_events xe
WHERE xe.event_id = geh.event_id
AND geh.entity_code = ‘REVALUATION’
AND geh.event_type_code = ‘LOTCOSTADJ’
AND geh.transaction_id IN
(SELECT lca.adjustment_id
FROM gmf_lot_cost_adjustment_dtls lcad,
gmf_lot_cost_adjustments lca,
cm_cmpt_mst cmpt,
cm_mthd_mst m
WHERE lca.cost_type_id = m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
AND lca.adjustment_date >= TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND lca.adjustment_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND lcad.adjustment_id = lca.adjustment_id
AND cmpt.cost_cmpntcls_id = lcad.cost_cmpntcls_id
AND lca.inventory_item_id =
&&inv_item_id
AND lca.organization_id =
&&organization_id
);
Extract Header (GLCOSTALOC)
=====================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code = ‘REVALUATION’
AND geh.event_type_code = ‘GLCOSTALOC’
AND geh.transaction_id IN
(SELECT dtl.allocdtl_id
FROM gl_aloc_dtl dtl,
gl_aloc_bas bas,
gl_aloc_mst mst,
gmf_period_statuses gps,
cm_cmpt_mst cmpt ,
hr_organization_information hoi ,
cm_mthd_mst m
WHERE mst.legal_entity_id = hoi.org_information2
AND hoi.organization_id =
bas.organization_id
AND hoi.org_information_context = ‘Accounting Information’
AND bas.inventory_item_id =
&&inv_item_id
AND bas.organization_id =
&&organization_id
AND cmpt.cost_cmpntcls_id = bas.cmpntcls_id
AND dtl.alloc_id =
mst.alloc_id
AND dtl.alloc_id =
bas.alloc_id
AND dtl.line_no =
bas.line_no
AND dtl.cost_type_id = gps.cost_type_id
AND gps.period_id =
dtl.period_id
AND gps.start_date >=
TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND gps.end_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND gps.legal_entity_id= hoi.org_information2
AND gps.cost_type_id = m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
AND gps.delete_mark = 0
);
Extract Lines (GLCOSTALOC)
====================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
gmf.gmf_xla_extract_lines gel
WHERE gel.header_id = geh.header_id
AND geh.entity_code = ‘REVALUATION’
AND geh.event_type_code = ‘GLCOSTALOC’
AND geh.transaction_id IN
(SELECT dtl.allocdtl_id
FROM gl_aloc_dtl dtl,
gl_aloc_bas bas,
gl_aloc_mst mst,
gmf_period_statuses gps,
cm_cmpt_mst cmpt ,
hr_organization_information hoi ,
cm_mthd_mst m
WHERE mst.legal_entity_id =
hoi.org_information2
AND hoi.organization_id =
bas.organization_id
AND hoi.org_information_context = ‘Accounting Information’
AND bas.inventory_item_id =
&&inv_item_id
AND bas.organization_id =
&&organization_id
AND cmpt.cost_cmpntcls_id = bas.cmpntcls_id
AND dtl.alloc_id =
mst.alloc_id
AND dtl.alloc_id =
bas.alloc_id
AND dtl.line_no =
bas.line_no
AND dtl.cost_type_id = gps.cost_type_id
AND gps.period_id =
dtl.period_id
AND gps.start_date >=
TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND gps.end_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND gps.legal_entity_id= hoi.org_information2
AND gps.cost_type_id = m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
AND gps.delete_mark = 0
);
SLA Events (GLCOSTALOC)
==================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
xla.xla_events xe
WHERE xe.event_id = geh.event_id
AND geh.entity_code = ‘REVALUATION’
AND geh.event_type_code = ‘GLCOSTALOC’
AND geh.transaction_id IN
(SELECT dtl.allocdtl_id
FROM gl_aloc_dtl dtl,
gl_aloc_bas bas,
gl_aloc_mst mst,
gmf_period_statuses gps,
cm_cmpt_mst cmpt ,
hr_organization_information hoi ,
cm_mthd_mst m
WHERE mst.legal_entity_id =
hoi.org_information2
AND hoi.organization_id =
bas.organization_id
AND hoi.org_information_context = ‘Accounting Information’
AND bas.inventory_item_id =
&&inv_item_id
AND bas.organization_id =
&&organization_id
AND cmpt.cost_cmpntcls_id = bas.cmpntcls_id
AND dtl.alloc_id =
mst.alloc_id
AND dtl.alloc_id = bas.alloc_id
AND dtl.line_no =
bas.line_no
AND dtl.cost_type_id = gps.cost_type_id
AND gps.period_id =
dtl.period_id
AND gps.start_date >=
TO_DATE(‘&&datefrom’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND gps.end_date <= TO_DATE(‘&&dateto’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND gps.legal_entity_id= hoi.org_information2
AND gps.cost_type_id = m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
AND gps.delete_mark = 0
);
ORDER MANAGEMENT RELATED:
ORDER header info FOR sales ORDER NUMBER: &ord_num
=====================================
SELECT o.name operating_unit,
h.header_id,
h.ship_from_org_id inv_org,
h.order_type_id,
h.flow_status_code status,
h.open_flag,
booked_flag,
h.cancelled_flag,
h.ordered_date ord_dt
FROM oe_order_headers_all h,
hr_operating_units o
WHERE header_id =
&hid
AND o.organization_id
= h.org_id;
ORDER line details FOR sales ORDER NUMBER: &ord_num AND
line_id: &lineid
==================================================
SELECT rtrim(l.line_number
||’.’
|| l.shipment_number
||’.’
|| l.option_number
||’.’
|| l.component_number
||’.’
|| l.service_number, ‘.’) line_num,
l.line_id,
l.split_from_line_id,
l.split_by,
l.flow_status_code status,
l.source_document_type_id,
l.org_id,
l.ordered_item item,
l.ordered_quantity qty1,
l.order_quantity_uom um1,
l.ordered_quantity2 qty2,
l.ordered_quantity_uom2 um2,
l.shipped_quantity sqty,
l.shipping_quantity sgqty,
l.ship_tolerance_above sta,
l.ship_tolerance_below stb,
l.fulfilled_flag ful_flag,
l.shipping_interfaced_flag ship_int_flag,
l.fulfilled_quantity fqty,
l.invoiced_quantity invoice_qty,
l.invoice_interface_status_code inv_int_sta_code,
l.open_flag,
l.booked_flag,
l.cancelled_quantity cqty,
l.cancelled_flag,
l.inventory_item_id,
l.ship_from_org_id,
w.organization_code,
w.process_enabled_flag,–w.loct_ctl wlc,
m.lot_divisible_flag,
m.tracking_quantity_ind,
m.dual_uom_control dualum_ind,
m.secondary_default_ind,
m.primary_uom_code uom,
m.secondary_uom_code uom2,
m.dual_uom_deviation_high,
m.dual_uom_deviation_low,
m.lot_control_code lot_ctl,
m.child_lot_flag sublot_ctl,
m.location_control_code loct_ctl,
m.grade_control_flag grade_ctl,
m.lot_status_enabled status_ctl,
–i.lot_indivisible, i.noninv_ind, i.dualum_ind, i.lot_ctl,
i.sublot_ctl, i.loct_ctl,
–i.grade_ctl,i.status_ctl,
DECODE(m.ont_pricing_qty_source,’P’,’Primary’,’Secondary’)
ont_pricing_qty_source,
l.source_document_line_id sdli,
l.source_type_code src_type,
ott.name line_type,
TO_CHAR(l.creation_date,’dd-mon-yyyy hh24:mi:ss’) cr_dt,
TO_CHAR(l.last_update_date,’dd-mon-yyyy hh24:mi:ss’) upd_dt
FROM oe_order_lines_all l,
mtl_parameters w,
mtl_system_items_b m,
oe_transaction_types_tl ott
WHERE l.header_id =
&hid
AND l.line_id LIKE DECODE(‘&lineid’,’all’,’%’,’&lineid’)
AND l.ship_from_org_id = w.organization_id
AND l.inventory_item_id = m.inventory_item_id
AND l.ship_from_org_id = m.organization_id
AND l.line_type_id = ott.transaction_type_id
AND ott.language =
( SELECT fl.language_code FROM fnd_languages fl WHERE
fl.installed_flag = ‘B’
)
ORDER BY l.line_id;
Delivery line details FOR sales ORDER NUMBER: &ord_num AND
line_id: &lineid
====================================================
SELECT source_line_number,
source_line_id,
delivery_detail_id,
split_from_delivery_detail_id,
move_order_line_id,
DECODE(rele ased_status,’R’,’R-Ready for rel’,’S’,’S-Rel to
whse’,’Y’,’Y-Staged’,
‘C’,’C-Shipped’,’B’,’B-Backordered’,’D’,’D-Cancelled’,released_status) rel_sts,
requested_quantity,
requested_quantity_uom,
requested_quantity2,
requested_quantity_uom2,
picked_quantity,
picked_quantity2,
shipped_quantity,
shipped_quantity2,
lot_number lotno,
sublot_number slotno,
oe_interfaced_flag oeif,
inv_interfaced_flag invif,
ship_tolerance_above sta,
ship_tolerance_below stb,
ship_set_id,
inventory_item_id,
src_requested_quantity,
src_requested_quantity_uom,
src_requested_quantity2,
src_requested_quantity_uom2,
organization_id inv_org,
cancelled_quantity,
cancelled_quantity2,
delivered_quantity,
delivered_quantity2,
TO_CHAR(creation_date,’dd-mon-yyyy hh24:mi:ss’) cr_dt,
TO_CHAR(last_update_date,’dd-mon-yyyy hh24:mi:ss’) upd_dt
FROM wsh_delivery_details
WHERE source_header_id =
&hid
AND source_line_id LIKE
DECODE(‘&lineid’,’all’,’%’,’&lineid’)
ORDER BY source_line_id,
delivery_detail_id;
Reservation details FOR sales ORDER NUMBER: &ord_num AND
line_id: &lineid
====================================================
SELECT mso.sales_order_id,
typ.name
INTO :sales_ord_id,
:ord_type_name
FROM mtl_sales_orders mso,
oe_order_headers_all ord,
oe_transaction_types_tl typ
WHERE ord.header_id =
&hid
AND ord.order_type_id = typ.transaction_type_id
AND language =
( SELECT fl.language_code FROM fnd_languages fl WHERE
fl.installed_flag = ‘B’
)
AND mso.segment1 =
&ord_num
AND typ.name = mso.segment2;
dbms_output.put_line(‘sales order id order number order type ‘);
dbms_output.put_line(‘——————————————————————‘);
dbms_output.put_line(rpad(TO_CHAR(:sales_ord_id), 16, ‘
‘)||rpad(‘&ord_num’, 20, ‘ ‘)||:ord_type_name);
END;
/
SELECT res.reservation_id reserv_id,
DECODE(res.ship_ready_flag, 1,’1=released’,
2,’2=submitted’, TO_CHAR(res.ship_ready_flag)) ship_ready,
res.demand_source_header_id ds_head_id,
TO_CHAR(LIN.line_number)
|| DECODE(LIN.shipment_number, NULL, NULL, ‘.’
|| TO_CHAR(LIN.shipment_number))
|| DECODE(LIN.option_number, NULL, NULL, ‘.’
|| TO_CHAR(LIN.option_number))
|| DECODE(LIN.component_number, NULL, NULL,
DECODE(LIN.option_number, NULL, ‘.’,NULL)
|| ‘.’
||TO_CHAR(LIN.component_number))
|| DECODE(LIN.service_number,NULL,NULL,
DECODE(LIN.component_number, NULL, ‘.’ , NULL)
|| DECODE(LIN.option_number, NULL, ‘.’, NULL )
|| ‘.’
|| TO_CHAR(LIN.service_number)) LINE,
res.demand_source_line_id ds_line_id,
res.primary_reservation_quantity res_q,
res.primary_uom_code uom,
res.secondary_reservation_quantity sec_res_q,
inv_convert.inv_um_convert( res.inventory_item_id,
res.lot_number, res.organization_id, 5, res.primary_reservation_quantity,
res.primary_uom_code, res.secondary_uom_code, NULL, NULL) calc_sec_res_q,
res.secondary_uom_code uom2,
res.lot_number lot_num,
res.organization_id orgn_id,
res.subinventory_code subinv,
— res.revision rev,
res.locator_id loc_id,
res.detailed_quantity dtl_q,
res.secondary_detailed_quantity sec_dtl_q,
res.inventory_item_id inventory_item_id,
itm.segment1 item,
res.requirement_date requird_d,
res.demand_source_delivery ds_deliv,
res.demand_source_type_id ds_type,
— res.serial_number serial_num,
res.supply_source_header_id ss_header_id,
res.supply_source_line_id ss_source_line,
res.supply_source_line_detail ss_source_line_det
–enable_timestamp ,to_char(res.creation_date,’dd-mon
hh24:mi:ss’) create_dt
–enable_timestamp ,to_char(res.last_update_date,’dd-mon
hh24:mi:ss’) update_dt
–enable_timestamp ,res.request_id request_id
FROM mtl_reservations res,
oe_order_lines_all lin,
mtl_system_items_b itm
WHERE res.demand_source_header_id
= :sales_ord_id
AND res.demand_source_type_id
IN (2,8,9,21,22)
AND res.demand_source_line_id
= lin.line_id(+)
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’) IN
(‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id,
lin.reference_line_id, lin.service_reference_line_id)
AND res.organization_id
= itm.organization_id(+)
AND res.inventory_item_id
= itm.inventory_item_id(+)
ORDER BY NVL(lin.top_model_line_id, lin.line_id),
NVL(lin.ato_line_id, lin.line_id),
NVL(lin.sort_order, ‘0000’),
NVL(lin.link_to_line_id, lin.line_id),
NVL(lin.source_document_line_id, lin.line_id),
lin.line_id,
res.reservation_id;
Allocation details FOR sales ORDER NUMBER: &ord_num AND
line_id: &lineid
==================================================
SELECT tmp.transaction_temp_id mtl_trns_id,
tmp.move_order_line_id move_line_id,
itm.segment1 item,
TO_CHAR(LIN.line_number)
|| DECODE(LIN.shipment_number, NULL, NULL, ‘.’
|| TO_CHAR(LIN.shipment_number))
|| DECODE(LIN.option_number, NULL, NULL, ‘.’
|| TO_CHAR(LIN.option_number))
|| DECODE(LIN.component_number, NULL, NULL,
DECODE(LIN.option_number, NULL, ‘.’,NULL)
|| ‘.’
||TO_CHAR(LIN.component_number))
|| DECODE(LIN.service_number,NULL,NULL,
DECODE(LIN.component_number, NULL, ‘.’ , NULL)
|| DECODE(LIN.option_number, NULL, ‘.’, NULL )
|| ‘.’
|| TO_CHAR(LIN.service_number)) LINE,
lin.line_id line_id,
tmp.primary_quantity prm_q,
tmp.secondary_transaction_quantity sec_q,
inv_convert.inv_um_convert( tmp.inventory_item_id, 5,
tmp.primary_quantity, itm.primary_uom_code, tmp.secondary_uom_code, NULL, NULL)
tmp_cal_sec_q,
lot.primary_quantity lot_prm_q,
lot.secondary_quantity lot_sec_q,
inv_convert.inv_um_convert( tmp.inventory_item_id,
lot.lot_number, tmp.organization_id, 5, NVL(lot.primary_quantity,0),
itm.primary_uom_code, tmp.secondary_uom_code, NULL, NULL) lot_cal_sec_q,
tmp.secondary_uom_code uom2,
lot.lot_number lot_num,
tmp.subinventory_code from_sub,
tmp.locator_id from_loc_id,
tmp.pick_slip_number pick_slip,
tmp.transfer_subinventory to_sub,
tmp.transfer_to_location to_loc_id,
tmp.process_flag process,
tmp.lock_flag lck,
tmp.transaction_mode trans_mode,
tmp.error_code error_code,
tmp.error_explanation error_expl
FROM mtl_material_transactions_temp tmp,
mtl_transaction_lots_temp lot,
oe_order_lines_all lin,
mtl_system_items_b itm
WHERE tmp.demand_source_line = lin.line_id
AND lin.line_category_code = ‘ORDER’
AND lin.ship_from_org_id =
itm.organization_id(+)
AND lin.inventory_item_id = itm.inventory_item_id(+)
AND lot.transaction_temp_id (+)= tmp.transaction_temp_id
AND lin.header_id
=
&hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’) IN
(‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id,
lin.reference_line_id, lin.service_reference_line_id);
–create index mtl_material_transactions_n99 on
mtl_material_transactions(trx_source_line_id);
prompt mtl_material_transactions (trn) – picked lines
PROMPT
— This is commented out because it runs slowly without an index
–<do not run> CREATE INDEX MTL_MATL_TRANS_777
–<do not run> ON INV.MTL_MATERIAL_TRANSACTIONS
–<do not run> (trx_source_line_id);
SELECT
/*moac_sql_no_changes*/
trn.transaction_id mtl_trns_id,
trn.move_order_line_id move_line_id,
trn.OPM_COSTED_FLAG,
trn.SHIPMENT_COSTED,
trn.SO_ISSUE_ACCOUNT_TYPE,
trn.COGS_RECOGNITION_PERCENT,
trn.TRANSFER_PRICE,
trn.FOB_POINT,
trn.OWNING_ORGANIZATION_ID,
trn.TRANSFER_ORGANIZATION_ID,
trn.TRANSACTION_ACTION_ID,
trn.TRANSACTION_SOURCE_TYPE_ID,
trn.TRANSACTION_TYPE_ID,
trn.TRANSACTION_QUANTITY,
trn.TRANSACTION_UOM,
trn.TRANSACTION_DATE,
trn.TRANSFER_TRANSACTION_ID,
cst.ACCTG_COST,
cst.period_id,
DECODE(trn.transaction_type_id, 52,’52=stage trans’,
33,’33=so issue’, trn.transaction_type_id) trans_type,
(SELECT TO_CHAR(LIN.line_number)
|| DECODE(LIN.shipment_number, NULL, NULL, ‘.’
|| TO_CHAR(LIN.shipment_number))
|| DECODE(LIN.option_number, NULL, NULL, ‘.’
|| TO_CHAR(LIN.option_number))
|| DECODE(LIN.component_number, NULL, NULL,
DECODE(LIN.option_number, NULL, ‘.’,NULL)
|| ‘.’
||TO_CHAR(LIN.component_number))
|| DECODE(LIN.service_number,NULL,NULL,
DECODE(LIN.component_number, NULL, ‘.’ , NULL)
|| DECODE(LIN.option_number, NULL, ‘.’, NULL )
|| ‘.’
|| TO_CHAR(LIN.service_number))
FROM oe_order_lines_all lin
WHERE trn.trx_source_line_id = lin.line_id
) line,
trn.trx_source_line_id line_id,
trn.primary_quantity prm_q,
trn.secondary_transaction_quantity sec_q,
inv_convert.inv_um_convert( trn.inventory_item_id, 5,
trn.primary_quantity, itm.primary_uom_code, trn.secondary_uom_code, NULL, NULL)
trn_cal_sec_q,
lot.primary_quantity lot_prm_q,
lot.secondary_transaction_quantity lot_sec_q,
inv_convert.inv_um_convert( trn.inventory_item_id, lot.lot_number,
trn.organization_id, 5, NVL(lot.primary_quantity,0), itm.primary_uom_code,
trn.secondary_uom_code, NULL, NULL) lot_cal_sec_q,
trn.secondary_uom_code uom2,
lot.lot_number lot_num,
trn.subinventory_code from_sub,
trn.locator_id from_loc_id,
trn.pick_slip_number pick_slip,
trn.transfer_subinventory to_sub,
trn.transfer_locator_id to_loc_id,
trn.organization_id orgn_id,
trn.transaction_source_id
FROM mtl_material_transactions trn,
mtl_transaction_lot_numbers lot,
mtl_system_items_b itm,
gl_item_cst cst,
gmf_fiscal_policies gfp,
gmf_period_statuses gps,
gmf_organization_definitions god,
cm_mthd_mst cmm
WHERE trn.trx_source_line_id IN
(SELECT DISTINCT line_id
FROM oe_order_lines_all lin1
WHERE lin1.header_id =
&hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’) IN
(‘%’,lin1.line_id, lin1.top_model_line_id, lin1.ato_line_id,
lin1.link_to_line_id, lin1.reference_line_id, lin1.service_reference_line_id)
)
AND trn.organization_id =
itm.organization_id
AND trn.inventory_item_id =
itm.inventory_item_id
AND lot.transaction_id (+) =
trn.transaction_id
AND trn.transaction_source_type_id = 2
AND gfp.cost_type_id
= cmm.cost_type_id
AND cst.period_id
= gps.period_id
AND gps.legal_entity_id =
gfp.legal_entity_id
AND gfp.legal_entity_id =
god.legal_entity_id
AND trn.organization_id =
god.organization_id
AND trn.transaction_date >=
gps.start_date
AND trn.transaction_date <=
gps.end_date
ORDER BY trn.trx_source_line_id,
trn.transaction_id;
prompt mtl_transactions_interface (mti)
prompt
SELECT
/*MOAC_SQL_NO_CHANGES*/
TO_CHAR(LIN.line_number)
|| DECODE(LIN.shipment_number, NULL, NULL, ‘.’
|| TO_CHAR(LIN.shipment_number))
|| DECODE(LIN.option_number, NULL, NULL, ‘.’
|| TO_CHAR(LIN.option_number))
|| DECODE(LIN.component_number, NULL, NULL,
DECODE(LIN.option_number, NULL, ‘.’,NULL)
|| ‘.’
||TO_CHAR(LIN.component_number))
|| DECODE(LIN.service_number,NULL,NULL,
DECODE(LIN.component_number, NULL, ‘.’ , NULL)
|| DECODE(LIN.option_number, NULL, ‘.’, NULL )
|| ‘.’
|| TO_CHAR(LIN.service_number)) LINE,
lin.line_id line_id,
det.delivery_detail_id del_detail_id,
itm.segment1 item,
tmp.primary_quantity prm_q,
secondary_transaction_quantity sec_q,
tmp.subinventory_code from_sub,
tmp.locator_id from_loc_id,
tmp.process_flag process,
tmp.lock_flag lck,
tmp.transaction_mode trans_mode,
tmp.error_code error_code,
tmp.error_explanation error_expl
FROM mtl_transactions_interface tmp,
wsh_delivery_details det,
oe_order_lines_all lin,
mtl_system_items_b itm
WHERE tmp.source_line_id = lin.line_id
AND lin.line_category_code = ‘order’
AND lin.ship_from_org_id = itm.organization_id(+)
AND lin.inventory_item_id = itm.inventory_item_id(+)
AND det.source_line_id = lin.line_id
AND lin.header_id =
&hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’) IN
(‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id,
lin.reference_line_id, lin.service_reference_line_id)
UNION ALL
SELECT TO_CHAR(LIN.line_number)
|| DECODE(LIN.shipment_number, NULL, NULL, ‘.’
|| TO_CHAR(LIN.shipment_number))
|| DECODE(LIN.option_number, NULL, NULL, ‘.’
|| TO_CHAR(LIN.option_number))
|| DECODE(LIN.component_number, NULL, NULL,
DECODE(LIN.option_number, NULL, ‘.’,NULL)
|| ‘.’
||TO_CHAR(LIN.component_number))
|| DECODE(LIN.service_number,NULL,NULL,
DECODE(LIN.component_number, NULL, ‘.’ , NULL)
|| DECODE(LIN.option_number, NULL, ‘.’, NULL )
|| ‘.’
|| TO_CHAR(LIN.service_number)) LINE,
lin.line_id line_id,
det.delivery_detail_id del_detail_id,
itm.segment1 item,
tmp.primary_quantity prm_q,
tmp.secondary_transaction_quantity sec_q,
tmp.subinventory_code from_sub,
tmp.locator_id from_loc_id,
tmp.process_flag process,
tmp.lock_flag lck,
tmp.transaction_mode trans_mode,
tmp.error_code error_code,
tmp.error_explanation error_expl
FROM mtl_transactions_interface tmp,
wsh_delivery_details det,
oe_order_lines_all lin,
mtl_system_items_b itm
WHERE tmp.trx_source_line_id = lin.line_id
AND lin.line_category_code = ‘return’
AND lin.ship_from_org_id = itm.organization_id(+)
AND lin.inventory_item_id = itm.inventory_item_id(+)
AND det.source_line_id = lin.line_id
AND lin.header_id =
&hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’)
IN
(‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id,
lin.reference_line_id, lin.service_reference_line_id);
Trip details FOR sales ORDER NUMBER: &ord_num AND line_id:
&lineid
==============================================
SELECT wdd.source_header_id,
wdd.source_line_id,
assign.delivery_assignment_id,
assign.delivery_id,
assign.delivery_detail_id,
deli.name deli_name,
deli.status_code deli_status,
legs.delivery_leg_id,
legs.pick_up_stop_id,
— legs.drop_off_stop_id,
DECODE(stops.pending_interface_flag, NULL, ‘Not Pending’,
‘Y’, ‘Pending’, stops.pending_interface_flag) pif,
stops.status_code trip_stop_status,
stops.actual_departure_date,
trips.name trip_name,
trips.trip_id,
trips.status_code trip_status
FROM wsh_delivery_details wdd,
wsh_delivery_assignments assign,
wsh_new_deliveries deli,
wsh_delivery_legs legs,
wsh_trip_stops stops,
wsh_trips trips
WHERE wdd.source_header_id =
&hid
AND wdd.source_line_id LIKE
DECODE(‘&lineid’,’all’,’%’,’&lineid’)
AND wdd.delivery_detail_id =assign.delivery_detail_id
AND assign.delivery_id = deli.delivery_id
AND deli.delivery_id =legs.delivery_id
AND legs.pick_up_stop_id = stops.stop_id
AND stops.trip_id =
trips.trip_id
ORDER BY assign.delivery_assignment_id,
assign.delivery_id,
assign.delivery_detail_id;
Move ORDER details FOR sales ORDER NUMBER: &ord_num AND line_id:
&lineid
===================================================
SELECT DISTINCT trl.line_id mo_line_id,
trh.request_number mo_number,
— trl.header_id mv_hdr_id,
trl.line_number mv_line_num,
DECODE(trl.line_status, 1, ‘1=Incomplete’, 2, ‘2=Pend Aprvl’,
3, ‘3=Approved’, 4, ‘4=Not Apprvd’, 5, ‘5=Closed’, 6, ‘6=Canceled’, 7, ‘7=Pre
Apprvd’, 8, ‘8=Part Aprvd’) mv_line_stat,
TO_CHAR(LIN.line_number)
|| DECODE(LIN.shipment_number, NULL, NULL, ‘.’
|| TO_CHAR(LIN.shipment_number))
|| DECODE(LIN.option_number, NULL, NULL, ‘.’
|| TO_CHAR(LIN.option_number))
|| DECODE(LIN.component_number, NULL, NULL,
DECODE(LIN.option_number, NULL, ‘.’,NULL)
|| ‘.’
||TO_CHAR(LIN.component_number))
|| DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number,
NULL, ‘.’ , NULL)
|| DECODE(LIN.option_number, NULL, ‘.’, NULL )
|| ‘.’
|| TO_CHAR(LIN.service_number)) LINE,
trl.txn_source_line_id ord_line_id,
det.delivery_detail_id ,
itm.segment1 item,
trl.quantity qty,
trl.primary_quantity prm_q,
trl.quantity_delivered dlv_q,
trl.quantity_detailed dtl_q,
trl.secondary_quantity sec_q,
trl.secondary_quantity_detailed sec_dtl_q,
trl.secondary_quantity_delivered sec_dlv_q,
trl.move_order_type_name move_type_name,
DECODE(trl.transaction_source_type_id,2,’Sales
Order’,trl.transaction_source_type_id) trns_src_type,
trl.transaction_type_name trns_type_name,
trl.organization_id orgn_id,
trl.from_subinventory_code from_sub,
trl.from_locator_id from_loc_id,
trl.to_subinventory_code to_sub,
trl.to_locator_id to_loc_id,
trl.lot_number lot_num,
trl.transaction_header_id trns_head_id
FROM mtl_txn_request_lines_v trl,
mtl_txn_request_headers trh,
wsh_delivery_details det,
oe_order_lines_all lin,
mtl_system_items_b itm
WHERE trl.line_id = det.move_order_line_id
–trl.txn_source_line_id = lin.line_id
AND lin.ship_from_org_id = itm.organization_id(+)
AND lin.inventory_item_id = itm.inventory_item_id(+)
AND det.source_line_id = lin.line_id
AND trl.header_id = trh.header_id
AND lin.header_id =
&hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’)
IN (‘%’,lin.line_id, lin.top_model_line_id,
lin.ato_line_id, lin.link_to_line_id, lin.reference_line_id,
lin.service_reference_line_id);
Extract Header details FOR sales ORDER NUMBER: &ord_num
========================================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh,
inv.mtl_material_transactions mmt
WHERE geh.transaction_id =
mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id IN
(SELECT DISTINCT line_id FROM oe_order_lines_all WHERE
header_id = &hid
);
Extract Lines FOR sales ORDER NUMBER: &ord_num
==================================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
inv.mtl_material_transactions mmt,
gmf.gmf_xla_extract_lines gel
WHERE gel.header_id
= geh.header_id
AND geh.transaction_id =
mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id IN
(SELECT DISTINCT line_id FROM oe_order_lines_all WHERE
header_id = &hid
);
SLA Events FOR sales ORDER NUMBER: &ord_num
=================================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
inv.mtl_material_transactions mmt ,
xla.xla_events xe
WHERE xe.event_id
= geh.event_id
AND geh.transaction_id =
mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id IN
(SELECT DISTINCT line_id FROM oe_order_lines_all WHERE
header_id = &hid
);
SLA Headers FOR sales ORDER NUMBER: &ord_num
==================================
SELECT ah.*
FROM xla.xla_ae_headers ah
WHERE ah.application_id = 555
AND ah.event_id IN
(SELECT geh.event_id
FROM gmf.gmf_xla_extract_headers geh,
inv.mtl_material_transactions mmt
WHERE geh.transaction_id
= mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id IN
(SELECT DISTINCT line_id FROM oe_order_lines_all
WHERE header_id = &hid
)
);
SLA Lines FOR sales ORDER NUMBER: &ord_num
================================
SELECT al.*
FROM xla.xla_ae_headers ah ,
xla.xla_ae_lines al
WHERE al.ae_header_id = ah.ae_header_id
AND ah.application_id = 555
AND ah.event_id IN
(SELECT geh.event_id
FROM gmf.gmf_xla_extract_headers geh,
inv.mtl_material_transactions mmt
WHERE geh.transaction_id
= mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id IN
(SELECT DISTINCT line_id FROM oe_order_lines_all
WHERE header_id = &hid
)
);
SLA Distributions FOR sales ORDER NUMBER: &ord_num
=====================================
SELECT dl.*
FROM gmf.gmf_xla_extract_headers geh,
inv.mtl_material_transactions mmt,
xla.xla_distribution_links dl
WHERE dl.event_id
= geh.event_id
AND dl.application_id
= 555
AND geh.transaction_id =
mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id IN
(SELECT DISTINCT line_id FROM oe_order_lines_all WHERE
header_id = &hid
);
Item Cost details
============
SELECT a.*
FROM gl_item_dtl a
WHERE a.itemcost_id IN
(SELECT itemcost_id
FROM gl_item_cst
WHERE (inventory_item_id, organization_id,cost_type_id,
period_id) IN
(SELECT DISTINCT mmt.inventory_item_id,
mmt.organization_id,
gps.cost_type_id,
gps.period_id
FROM gmf_organization_definitions god,
gmf_period_statuses gps,
gmf_fiscal_policies gfp,
cm_mthd_mst mthd,
mtl_material_transactions mmt
WHERE mmt.transaction_source_type_id = 2
AND god.organization_id
= mmt.organization_id
AND gfp.legal_entity_id
= god.legal_entity_id
AND mthd.cost_type_id
= gfp.cost_type_id
AND gps.legal_entity_id
= gfp.legal_entity_id
AND gps.cost_type_id
= gfp.cost_type_id
AND mmt.transaction_date
>= gps.start_date
AND mmt.transaction_date
<= gps.end_date
AND mmt.trx_source_line_id
IN
(SELECT DISTINCT line_id FROM
oe_order_lines_all WHERE header_id = &hid
)
)
);
COGS RELATED:
Run Following Queries BY entering related SO# numbers AND RMA#
Numbers FOR which COGS/DCOGS being analized **/
OE Headers
==========
SELECT * FROM oe_order_headers_all WHERE order_number IN (
‘&so_number’);
OE lines
=====
SELECT oeh.order_number,
oeh.header_id,
oeh.order_type_id,
oel.*
FROM oe_order_headers_all oeh,
oe_order_lines_all oel
WHERE oel.header_id = oeh.header_id
AND oeh.order_number IN ( ‘&so_number’);
MMT rows
=======
SELECT oeh.order_number,
oeh.header_id oe_header_id,
oeh.order_type_id,
mmt.*
FROM oe_order_headers_all oeh,
oe_order_lines_all oel,
mtl_material_transactions mmt
WHERE mmt.trx_source_line_id =
oel.line_id
AND oel.header_id
= oeh.header_id
AND mmt.transaction_source_type_id IN (2, 12)
AND oeh.order_number
IN ( ‘&so_number’);
COGS events
===========
SELECT *
FROM cst_cogs_events
WHERE cogs_om_line_id IN
(SELECT oel.line_id
FROM oe_order_headers_all oeh,
oe_order_lines_all oel
WHERE oel.header_id = oeh.header_id
AND oeh.order_number IN ( ‘&so_number’)
) ;
Extract Headers
===========
SELECT oeh.order_number,
oeh.header_id oe_header_id,
oeh.order_type_id,
geh.*
FROM oe_order_headers_all oeh,
oe_order_lines_all oel,
mtl_material_transactions mmt,
gmf_xla_extract_headers geh
WHERE mmt.transaction_id
= geh.transaction_id
AND mmt.trx_source_line_id =
oel.line_id
AND oel.header_id
= oeh.header_id
AND mmt.transaction_source_type_id IN (2, 12)
AND oeh.order_number
IN ( ‘&so_number’);
Extract Lines
=========
SELECT oeh.order_number,
oeh.header_id oe_header_id,
oeh.order_type_id,
gel.*
FROM oe_order_headers_all oeh,
oe_order_lines_all oel,
mtl_material_transactions mmt,
gmf_xla_extract_headers geh,
gmf_xla_extract_lines gel
WHERE gel.header_id
= geh.header_id
AND geh.transaction_id
= mmt.transaction_id
AND mmt.trx_source_line_id
= oel.line_id
AND oel.header_id
= oeh.header_id
AND mmt.transaction_source_type_id IN (2, 12)
AND oeh.order_number
IN ( ‘&so_number’);
INVENTORY RELATED:
Material transactions
==============
SELECT *
FROM mtl_material_transactions
WHERE organization_id =
&&orgid
AND inventory_item_id =
&&invitemid
AND transaction_id =
&&transid;
Lot NUMBER transactions
=================
SELECT *
FROM mtl_transaction_lot_numbers
WHERE organization_id =
&&orgid
AND inventory_item_id =
&&invitemid
AND transaction_id =
&&transid;
MTL Parameter organization details Organization id:
&&orgid
==========================================
SELECT * FROM mtl_parameters WHERE organization_id =
&&orgid;
Item details FOR Organization id: &&orgid AND item id:
&&invitemid
==============================================
SELECT *
FROM mtl_system_items_kfv
WHERE organization_id =
&&orgid
AND inventory_item_id =
&&invitemid;
Extract Header details FOR Transaction id: &&transid
====================================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code = ‘INVENTORY’
AND geh.transaction_id IN
(SELECT t.transaction_id
FROM mtl_material_transactions t
WHERE t.transaction_id =
&&transid
);
Extract Lines details FOR Transaction id: &&transid
===================================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
gmf.gmf_xla_extract_lines gel
WHERE geh.entity_code = ‘INVENTORY’
AND gel.header_id = geh.header_id
AND geh.transaction_id IN
(SELECT t.transaction_id
FROM mtl_material_transactions t
WHERE t.transaction_id =
&&transid
);
Sla Events FOR Transaction id: &&transid
============================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
xla.xla_events xe
WHERE geh.entity_code = ‘INVENTORY’
AND xe.event_id = geh.event_id
AND geh.transaction_id IN
(SELECT t.transaction_id
FROM mtl_material_transactions t
WHERE t.transaction_id =
&&transid
);
Item Component Cost details FOR Transaction id:
&&transid
==========================================
SELECT a.*
FROM gl_item_dtl a
WHERE a.itemcost_id IN
(SELECT itemcost_id
FROM gl_item_cst
WHERE (inventory_item_id, organization_id,cost_type_id,
period_id) IN
(SELECT DISTINCT mmt.inventory_item_id,
mmt.organization_id,
gps.cost_type_id,
gps.period_id
FROM gmf_organization_definitions god,
gmf_period_statuses gps,
gmf_fiscal_policies gfp,
cm_mthd_mst mthd,
mtl_material_transactions mmt
WHERE god.organization_id = mmt.organization_id
AND mmt.transaction_id =
&&transid
AND mmt.organization_id =
&&orgid
AND mmt.inventory_item_id =
&&invitemid
AND gfp.legal_entity_id = god.legal_entity_id
AND mthd.cost_type_id =
gfp.cost_type_id
AND gps.legal_entity_id = gfp.legal_entity_id
AND gps.cost_type_id =
gfp.cost_type_id
AND mmt.transaction_date >= gps.start_date
AND mmt.transaction_date <= gps.end_date
)
);
PURCHASING RELATED:
PO Header details FOR purchase ORDER id:
&&poheaderid
=======================================
SELECT * FROM po.po_headers_all WHERE po_header_id =
&poheaderid;
PO Line details FOR purchase ORDER id: &&poheaderid
=====================================
SELECT * FROM po_lines_all WHERE po_header_id = &poheaderid;
PO Line location details FOR purchase ORDER id:
&&poheaderid
===========================================
SELECT * FROM po_line_locations WHERE po_header_id =
&poheaderid;
PO distribution details FOR purchase ORDER id:
&&poheaderid
==========================================
SELECT * FROM po_distributions WHERE po_header_id =
&poheaderid;
RCV Transactions FOR purchase ORDER id: &&poheaderid
=======================================
SELECT * FROM po.rcv_transactions WHERE po_header_id =
&&poheaderid;
RCV accounting txns FOR purchase ORDER id:
&&poheaderid
=========================================
SELECT * FROM gmf.gmf_rcv_accounting_txns WHERE po_header_id =
&&poheaderid;
MMT txns FOR purchase ORDER id: &&poheaderid
==================================
SELECT *
FROM inv.mtl_material_transactions
WHERE transaction_source_type_id = 1
AND rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
);
Opm financials data
===============
Extract Header details FOR purchase ORDER id:
&&poheaderid:
==========================================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh,
inv.mtl_material_transactions mmt
WHERE geh.transaction_id =
mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
)
UNION ALL
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh,
gmf.gmf_rcv_accounting_txns mmt
WHERE geh.transaction_id = mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
);
Extract Lines details FOR purchase ORDER id:
&&poheaderid
========================================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
inv.mtl_material_transactions mmt ,
gmf.gmf_xla_extract_lines gel
WHERE gel.header_id
= geh.header_id
AND geh.transaction_id =
mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
)
UNION ALL
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
gmf.gmf_rcv_accounting_txns mmt ,
gmf.gmf_xla_extract_lines gel
WHERE gel.header_id = geh.header_id
AND geh.transaction_id =
mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
);
Sla Events FOR purchase ORDER id: &&poheaderid
=================================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
inv.mtl_material_transactions mmt ,
xla.xla_events xe
WHERE xe.event_id
= geh.event_id
AND geh.transaction_id =
mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
)
UNION ALL
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
gmf.gmf_rcv_accounting_txns mmt,
xla.xla_events xe
WHERE xe.event_id =
geh.event_id
AND geh.transaction_id =
mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
);
Sla Header details FOR purchase ORDER id:
&&poheaderid
======================================
SELECT ah.*
FROM xla.xla_ae_headers ah
WHERE ah.application_id = 555
AND ah.event_id IN
(SELECT geh.event_id
FROM gmf.gmf_xla_extract_headers geh,
inv.mtl_material_transactions mmt
WHERE geh.transaction_id
= mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
)
)
UNION ALL
SELECT ah.*
FROM xla.xla_ae_headers ah
WHERE ah.application_id = 555
AND ah.event_id IN
(SELECT geh.event_id
FROM gmf.gmf_xla_extract_headers geh,
gmf.gmf_rcv_accounting_txns mmt
WHERE geh.transaction_id =
mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
)
);
Sla Lines details FOR purchase ORDER id: &&poheaderid
======================================
SELECT al.*
FROM xla.xla_ae_headers ah ,
xla.xla_ae_lines al
WHERE al.ae_header_id = ah.ae_header_id
AND ah.application_id = 555
AND ah.event_id IN
(SELECT geh.event_id
FROM gmf.gmf_xla_extract_headers geh,
inv.mtl_material_transactions mmt
WHERE geh.transaction_id
= mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
)
)
UNION ALL
SELECT al.*
FROM xla.xla_ae_headers ah ,
xla.xla_ae_lines al
WHERE al.ae_header_id = ah.ae_header_id
AND ah.application_id = 555
AND ah.event_id IN
(SELECT geh.event_id
FROM gmf.gmf_xla_extract_headers geh,
gmf.gmf_rcv_accounting_txns mmt
WHERE geh.transaction_id =
mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
)
);
Sla Distributions FOR purchase ORDER id:
&&poheaderid
======================================
SELECT dl.*
FROM gmf.gmf_xla_extract_headers geh,
inv.mtl_material_transactions mmt ,
xla.xla_distribution_links dl
WHERE dl.event_id
= geh.event_id
AND dl.application_id
= 555
AND geh.transaction_id =
mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
)
UNION ALL
SELECT dl.*
FROM gmf.gmf_xla_extract_headers geh,
gmf.gmf_rcv_accounting_txns mmt ,
xla.xla_distribution_links dl
WHERE dl.event_id =
geh.event_id
AND dl.application_id = 555
AND geh.transaction_id = mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
(SELECT transaction_id
FROM po.rcv_transactions
WHERE po_header_id =
&&poheaderid
);
Item Cost details
============
SELECT a.*
FROM gl_item_dtl a
WHERE a.itemcost_id IN
(SELECT itemcost_id
FROM gl_item_cst
WHERE (inventory_item_id, organization_id,cost_type_id,
period_id) IN
(SELECT DISTINCT mmt.inventory_item_id,
mmt.organization_id,
gps.cost_type_id,
gps.period_id
FROM gmf_organization_definitions god,
gmf_period_statuses gps,
gmf_fiscal_policies gfp,
cm_mthd_mst mthd,
mtl_material_transactions mmt,
rcv_transactions rct
WHERE mmt.transaction_source_type_id = 1
AND god.organization_id
= mmt.organization_id
AND mmt.rcv_transaction_id
= rct.transaction_id
AND rct.po_header_id
=
&&poheaderid
AND gfp.legal_entity_id = god.legal_entity_id
AND mthd.cost_type_id =
gfp.cost_type_id
AND gps.legal_entity_id = gfp.legal_entity_id
AND gps.cost_type_id =
gfp.cost_type_id
AND mmt.transaction_date >= gps.start_date
AND mmt.transaction_date <= gps.end_date
)
);
PRODUCTION MANAGEMENT:
Batch Header details FOR Batch id: &&batch_id
================================
SELECT * FROM gme_batch_header WHERE
batch_id=’&&batch_id’;
Recipe details FOR Batch id: &&batch_id
============================
SELECT r.*
FROM gme_batch_header b ,
gmd_recipes r ,
gmd_recipe_validity_rules vr
WHERE b.batch_id=
&&batch_id
AND b.recipe_validity_rule_id
=vr.recipe_validity_rule_id
AND vr.recipe_id
=r.recipe_id;
Recipe Validity Rules details FOR Batch id:
&&batch_id
======================================
SELECT vr.*
FROM gme_batch_header b ,
gmd_recipes r ,
gmd_recipe_validity_rules vr
WHERE b.batch_id=
&&batch_id
AND b.recipe_validity_rule_id
=vr.recipe_validity_rule_id
AND vr.recipe_id
=r.recipe_id;
Batch Material Details FOR Batch id: &&batch_id
=================================
SELECT d.* FROM gme_material_details d WHERE d.batch_id =
&&batch_id;
Inventory Transactions details FOR Batch id:
&&batch_id
=======================================
SELECT t.*
FROM mtl_material_transactions t
WHERE t.transaction_source_id =
&&batch_id
AND t.transaction_source_type_id
=
5;
Batch Material Transaction Pairs FOR Batch NUMBER:
&&batch_id
=============================================
SELECT * FROM gme_transaction_pairs p WHERE p.batch_id =
&&batch_id;
Batch RESOURCE Transactions details FOR Batch NUMBER:
&&batch_id
===============================================
SELECT t.*
FROM gme_batch_header h,
gme_resource_txns t
WHERE h.batch_id =
&&batch_id
AND h.batch_id
= t.doc_id
AND t.doc_type
= ‘PROD’;
Yield Layers FOR Batch id: &&batch_id
===========================
SELECT *
FROM gmf_incoming_material_layers il
WHERE (il.mmt_organization_id, il.mmt_transaction_id) IN
(SELECT DISTINCT t.organization_id,
t.transaction_id
FROM mtl_material_transactions t
WHERE t.transaction_source_id =
&&batch_id
AND t.transaction_source_type_id = 5
);
Material Consumption Layers FOR Batch id: &&batch_id
=======================================
SELECT *
FROM gmf_outgoing_material_layers ol
WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
(SELECT DISTINCT t.organization_id,
t.transaction_id
FROM mtl_material_transactions t
WHERE t.transaction_source_id =
&&batch_id
AND t.transaction_source_type_id = 5
);
RESOURCE Consumption Layers FOR Batch id: &&batch_id
========================================
SELECT *
FROM gmf_resource_layers il
WHERE il.poc_trans_id IN
(SELECT t.poc_trans_id
FROM gme_resource_txns t
WHERE t.doc_id =
&&batch_id
AND t.doc_type = ‘PROD’
);
VIB Details FOR Batch id: &&batch_id
==========================
SELECT *
FROM gmf_batch_vib_details bvd
WHERE bvd.requirement_id IN
(SELECT br.requirement_id
FROM gmf_batch_requirements br
WHERE br.batch_id =
&&batch_id
);
Batch Requirement Details FOR Batch id: &&batch_id
=====================================
SELECT * FROM gmf_batch_requirements br WHERE br.batch_id =
&&batch_id;
Layer cost details FOR Batch id: &&batch_id
==============================
SELECT *
FROM gmf_layer_cost_details c
WHERE c.layer_id IN
(SELECT il.layer_id
FROM gme_batch_header h,
mtl_material_transactions t,
gmf_incoming_material_layers il
WHERE h.batch_id =
&&batch_id
AND h.batch_id
= t.transaction_source_id
AND t.transaction_source_type_id = 5
AND il.mmt_transaction_id =
t.transaction_id
AND il.mmt_organization_id =
t.organization_id
);
Extract Header details FOR Batch id: &&batch_id
=================================
SELECT *
FROM gmf_xla_extract_headers
WHERE entity_code = ‘PRODUCTION’
AND source_document_id =
&l_batch_id;
Extract Lines details FOR Batch id: &&batch_id
=================================
SELECT *
FROM gmf_xla_extract_lines
WHERE header_id IN
(SELECT header_id
FROM gmf_xla_extract_headers
WHERE entity_code = ‘PRODUCTION’
AND source_document_id =
&l_batch_id
);
Sla Events FOR Batch id: &&batch_id
==========================
SELECT xe.*
FROM gmf_xla_extract_headers eh,
xla_events xe
WHERE eh.event_id = xe.event_id
AND xe.application_id =555
AND eh.entity_code = ‘PRODUCTION’
AND eh.source_document_id =
&l_batch_id;
Item Component Cost details FOR Batch id: &&batch_id
=======================================
SELECT a.*
FROM gl_item_dtl a
WHERE a.itemcost_id IN
(SELECT itemcost_id
FROM gl_item_cst
WHERE (inventory_item_id, organization_id,cost_type_id,
period_id) IN
(SELECT DISTINCT mmt.inventory_item_id,
mmt.organization_id,
gps.cost_type_id,
gps.period_id
FROM gmf_organization_definitions god,
gmf_period_statuses gps,
gmf_fiscal_policies gfp,
cm_mthd_mst mthd,
mtl_material_transactions mmt
WHERE mmt.transaction_source_type_id = 5
AND god.organization_id
= mmt.organization_id
AND mmt.transaction_source_id
=
&&batch_id
AND gfp.legal_entity_id = god.legal_entity_id
AND mthd.cost_type_id =
gfp.cost_type_id
AND gps.legal_entity_id = gfp.legal_entity_id
AND gps.cost_type_id =
gfp.cost_type_id
AND mmt.transaction_date >= gps.start_date
AND mmt.transaction_date <= gps.end_date
)
);
QUERY FOR LINK BETWEEN SLA AND
OPM ACCOUNTING PREPROCESSOR TABLES IN R12
SELECT GEH.REFERENCE_NO,
GEH.TRANSACTION_ID,
GEH.TRANSACTION_DATE,
gel.line_number,
gel.journal_line_type,
gel.entered_amount,
gel.accounted_amount,
FND_FLEX_EXT.GET_SEGS(‘SQLGL’, ‘GL#’,gcc.chart_of_accounts_id,gcc.code_combination_id)
account,
GL_FLEXFIELDS_PKG.get_concat_description( gcc.chart_of_accounts_id,
gcc.code_combination_id) acc_description
FROM gl_code_combinations gcc,
xla_ae_lines al,
xla_distribution_links dl,
gmf_xla_extract_headers geh,
gmf_xla_extract_lines gel
WHERE gcc.code_combination_id = al.code_combination_id
AND al.ae_header_id =
dl.ae_header_id
AND al.ae_line_num
= dl.ae_line_num
AND dl.event_id
= geh.event_id
AND dl.application_id = 555
AND dl.source_distribution_type = geh.entity_code
AND dl.source_distribution_id_num_1 = gel.line_id
AND geh.header_id
= gel.header_id
and geh.event_id
= gel.event_id
–AND gel.journal_line_type IN (‘COGS’,’DCOG’)
AND geh.transaction_date >=
TO_DATE(‘&&fromDate’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’) –period start date 01/01/2011
AND geh.transaction_date <= TO_DATE(‘&&toDate’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’) –Period end date 01/31/2011
R12 – OPM OPERATIONS CREATION
USING API
DECLARE
CURSOR c_operation IS
SELECT DISTINCT oprn_no
, oprn_desc
, process_qty_uom
, oprn_vers
, delete_mark
, effective_start_date
, operation_status
, owner_organization_id
, activity
, offset_interval
, activity_factor
FROM mii_gmd_operations
WHERE flag IS NULL;
CURSOR c_resource (
p_oprn_no varchar2,
p_activity varchar2
) IS
SELECT resources,
process_qty,
resource_process_uom,
resource_usage,
resource_usage_uom,
cost_cmpntcls_id,
cost_analysis_code,
prim_rsrc_ind,
resource_count,
scale_type,
offset_interval
FROM mii_gmd_operations
WHERE oprn_no = p_oprn_no
AND activity = p_activity
ORDER BY PRIM_RSRC_IND;
l_operations gmd_operations%ROWTYPE;
l_oprn_actv_tbl
gmd_operations_pub.gmd_oprn_activities_tbl_type;
l_oprn_rsrc_tbl
gmd_operation_resources_pub.gmd_oprn_resources_tbl_type;
l_count NUMBER
:= 0;
l_count1 NUMBER
:= 0;
l_loop_cnt NUMBER
:= 0;
l_record_count NUMBER
:= 0;
l_data VARCHAR2 (2000);
l_data1 VARCHAR2 (2000);
l_return_status VARCHAR2 (1);
l_return_status1 VARCHAR2 (1);
l_status VARCHAR2 (1);
i NUMBER
:= 1;
l_dummy_cnt NUMBER;
l_api_version NUMBER
:= 1;
l_init_msg_list BOOLEAN;
l_commit BOOLEAN;
return_sts BOOLEAN;
v_oprn_line_id number;
v_count number;
v_resources
varchar2(100);
v_process_qty
number;
v_resource_process_uom varchar2(5);
v_resource_usage number;
v_resource_usage_uom varchar2(5);
v_cost_cmpntcls_id number;
v_cost_analysis_code varchar2(5);
v_prim_rsrc_ind
number;
v_resource_count number;
v_scale_type
number;
v_offset_interval number;
BEGIN
fnd_global.apps_initialize (user_id
=> 1090,
resp_id
=> 22882,
resp_appl_id => 552
);
l_init_msg_list := TRUE;
l_commit := TRUE;
FOR l_oprn IN c_operation
LOOP
DBMS_OUTPUT.put_line (‘Value Test ‘ || l_operations.oprn_no);
l_operations.oprn_no := l_oprn.oprn_no;
l_operations.oprn_desc := l_oprn.oprn_desc;
l_operations.process_qty_uom := l_oprn.process_qty_uom;
l_operations.oprn_vers := l_oprn.oprn_vers;
l_operations.delete_mark := l_oprn.delete_mark;
l_operations.effective_start_date :=
l_oprn.effective_start_date;
l_operations.operation_status := l_oprn.operation_status;
l_operations.owner_organization_id :=
l_oprn.owner_organization_id;
l_oprn_actv_tbl (1).activity := l_oprn.activity;
l_oprn_actv_tbl (1).offset_interval :=
l_oprn.offset_interval;
l_oprn_actv_tbl (1).activity_factor :=
l_oprn.activity_factor;
l_oprn_actv_tbl (1).delete_mark := l_oprn.delete_mark;
–l_oprn_actv_tbl (1).activity := l_master.activity;
SELECT COUNT(*)
INTO v_count
FROM mii_gmd_operations
WHERE oprn_no = l_oprn.oprn_no
AND activity = l_oprn.activity
ORDER BY PRIM_RSRC_IND;
OPEN c_resource (l_oprn.oprn_no, l_oprn.activity);
FOR i IN 1 .. v_count
LOOP
FETCH c_resource INTO v_resources,
v_process_qty,
v_resource_process_uom,
v_resource_usage,
v_resource_usage_uom,
v_cost_cmpntcls_id,
v_cost_analysis_code,
v_prim_rsrc_ind,
v_resource_count,
v_scale_type,
v_offset_interval;
l_oprn_rsrc_tbl (i).activity :=
l_oprn.activity;
l_oprn_rsrc_tbl (i).resources := v_resources;
l_oprn_rsrc_tbl (i).process_qty :=
v_process_qty;
l_oprn_rsrc_tbl (i).resource_process_uom :=
v_resource_process_uom;
l_oprn_rsrc_tbl (i).resource_usage :=
v_resource_usage;
l_oprn_rsrc_tbl (i).resource_usage_uom :=
v_resource_usage_uom;
l_oprn_rsrc_tbl (i).cost_cmpntcls_id :=
v_cost_cmpntcls_id;
l_oprn_rsrc_tbl (i).cost_analysis_code :=
v_cost_analysis_code;
l_oprn_rsrc_tbl (i).prim_rsrc_ind :=
v_prim_rsrc_ind;
l_oprn_rsrc_tbl (i).resource_count :=
v_resource_count;
l_oprn_rsrc_tbl (i).scale_type :=
v_scale_type;
l_oprn_rsrc_tbl (i).offset_interval :=
v_offset_interval;
UPDATE mii_gmd_operations
set flag = ‘Y’
WHERE oprn_no = l_oprn.oprn_no
AND resources = v_resources;
END LOOP;
CLOSE c_resource;
gmd_operations_pub.insert_operation (p_api_version
=> 1.0,
p_init_msg_list
=> l_init_msg_list,
p_commit
=> l_commit,
p_operations
=> l_operations,
p_oprn_actv_tbl
=> l_oprn_actv_tbl,
p_oprn_rsrc_tbl
=> l_oprn_rsrc_tbl,
x_message_count
=> l_count,
x_return_status
=> l_return_status,
x_message_list
=> l_data
);
UPDATE gmd_operations_b
set operation_status = 700
WHERE operation_status = 100
AND oprn_no = l_oprn.oprn_no;
IF l_status = ‘E’ OR l_status = ‘U’
THEN
UPDATE mii_gmd_operations
set flag = ‘E’
, note = l_data
WHERE oprn_no = l_oprn.oprn_no;
END IF;
END LOOP;
IF l_count >= 0
THEN
l_loop_cnt := 1;
LOOP
fnd_msg_pub.get (p_msg_index
=> l_loop_cnt,
p_data =>
l_data,
p_encoded =>
fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);
DBMS_OUTPUT.put_line (‘Record = ‘ ||
l_loop_cnt);
DBMS_OUTPUT.put_line (l_data);
IF l_status = ‘E’ OR
l_status = ‘U’
THEN
l_data := CONCAT (‘ERROR ‘, l_data);
END IF;
DBMS_OUTPUT.put_line
(l_data);
IF (l_status = ‘U’)
THEN
l_return_status := l_status;
ELSIF (l_status = ‘E’ AND l_return_status
<> ‘U’)
THEN
l_return_status := l_status;
ELSE
l_return_status := l_status;
END IF;
l_loop_cnt := l_loop_cnt +
1;
IF l_loop_cnt > l_count
THEN
EXIT;
END IF;
END LOOP;
END IF;
COMMIT;
DBMS_OUTPUT.put_line (‘Sucess’);
END;
/
R12 – OPM ROUTING CREATION
USING API
DECLARE
CURSOR c_routing_header IS
SELECT DISTINCT ROUTING_NO,
ROUTING_VERS,
ROUTING_DESC,
EFFECTIVE_START_DATE,
ROUTING_QTY,
ROUTING_UOM,
OWNER_ID,
OWNER_ORGANIZATION_ID,
ROUTING_STATUS,
DELETE_MARK
FROM mii_gmd_routing
WHERE flag IS NULL;
CURSOR c_routing_step (
p_routing_no varchar2
)
IS
SELECT ROUTINGSTEP_NO
, OPRN_NO
, STEP_QTY
, STEPRELEASE_TYPE
, ROUTINGSTEP_NO1
, DEP_ROUTINGSTEP_NO
, DEP_TYPE
, STANDARD_DELAY
, TRANSFER_PCT
, ROUTINGSTEP_NO_UOM
FROM mii_gmd_routing
WHERE flag IS NULL
AND routing_no = p_routing_no
ORDER BY routingstep_no;
l_routings
gmd_routings%ROWTYPE;
l_routings_step_tbl
gmd_routings_pub.gmd_routings_step_tab;
l_routings_step_dep_tbl
gmd_routings_pub.gmd_routings_step_dep_tab;
l_count
NUMBER
:= 0;
l_loop_cnt
NUMBER
:= 0;
l_record_count NUMBER
:= 0;
l_data
VARCHAR2 (4000);
l_return_status VARCHAR2 (1);
l_status
VARCHAR2 (1);
i
NUMBER
:=
1;
l_dummy_cnt
NUMBER;
l_api_version NUMBER
:= 1;
l_init_msg_list BOOLEAN;
l_commit
BOOLEAN;
return_sts
BOOLEAN;
v_routingstep_no number;
v_oprn_id
number;
v_oprn_no
varchar2(100);
v_step_qty
number;
v_steprelease_type number;
v_routingstep_no1 number;
v_dep_routingstep_no number;
v_dep_type
number;
v_standard_delay number;
v_transfer_pct number;
v_routingstep_no_uom
varchar2(100);
v_count
number;
BEGIN
fnd_global.apps_initialize (user_id
=> 1090,
resp_id
=> 22882,
resp_appl_id => 552
);
l_init_msg_list := TRUE;
l_commit := TRUE;
FOR l_routing_header IN c_routing_header
LOOP
l_routings.routing_no := l_routing_header.routing_no;
l_routings.routing_vers := l_routing_header.routing_vers;
l_routings.routing_desc := l_routing_header.routing_desc;
l_routings.routing_qty := l_routing_header.routing_qty;
–l_routings.item_um := l_routing_header.item_um;
l_routings.routing_uom := l_routing_header.routing_uom;
l_routings.effective_start_date :=
l_routing_header.effective_start_date;
l_routings.owner_id := l_routing_header.owner_id;
l_routings.routing_status :=
l_routing_header.routing_status;
l_routings.delete_mark := l_routing_header.delete_mark;
l_routings.owner_organization_id :=
l_routing_header.owner_organization_id;
SELECT COUNT(*)
INTO v_count
FROM mii_gmd_routing
WHERE flag IS NULL
AND routing_no = l_routing_header.routing_no;
OPEN c_routing_step (l_routing_header.routing_no);
FOR i IN 1 .. v_count
LOOP
FETCH c_routing_step INTO v_routingstep_no,
v_oprn_no,
v_step_qty,
v_steprelease_type,
v_routingstep_no1,
v_dep_routingstep_no,
v_dep_type,
v_standard_delay,
v_transfer_pct,
v_routingstep_no_uom;
SELECT oprn_id
INTO v_oprn_id
FROM GMD_OPERATIONS_VL
WHERE oprn_no = v_oprn_no;
–dbms_output.put_line (‘v_oprn_id: ‘ ||
v_oprn_id);
l_routings_step_tbl (i).routingstep_no :=
v_routingstep_no;
l_routings_step_tbl (i).oprn_id := v_oprn_id;
l_routings_step_tbl (i).step_qty :=
v_step_qty;
l_routings_step_tbl (i).steprelease_type :=
v_steprelease_type;
–dbms_output.put_line (‘l_routings_step_tbl
(‘||i||’).routingstep_no: ‘ || l_routings_step_tbl (i).routingstep_no);
–IF v_routingstep_no1 IS NOT NULL THEN
l_routings_step_dep_tbl
(i).routingstep_no := v_routingstep_no1;
l_routings_step_dep_tbl
(i).dep_routingstep_no := v_dep_routingstep_no;
l_routings_step_dep_tbl (i).dep_type
:= v_dep_type;
l_routings_step_dep_tbl
(i).standard_delay := v_standard_delay;
l_routings_step_dep_tbl
(i).transfer_pct := v_transfer_pct;
l_routings_step_dep_tbl
(i).routingstep_no_uom := v_routingstep_no_uom;
–END IF;
UPDATE mii_gmd_routing
set flag = ‘Y’
WHERE routing_no =
l_routing_header.routing_no
AND oprn_no = v_oprn_no;
END LOOP;
CLOSE c_routing_step;
–DBMS_OUTPUT.put_line (‘Value Test ‘ ||
l_routings.routing_no);
gmd_routings_pub.insert_routing
(p_api_version
=> 1.0,
p_init_msg_list
=> l_init_msg_list,
p_commit
=> l_commit,
p_routings
=> l_routings,
p_routings_step_tbl =>
l_routings_step_tbl,
p_routings_step_dep_tbl =>
l_routings_step_dep_tbl,
x_message_count
=> l_count,
x_return_status
=> l_return_status,
x_message_list
=> l_data
);
END LOOP;
IF l_count >= 0
THEN
l_loop_cnt := 1;
LOOP
fnd_msg_pub.get (p_msg_index
=> l_loop_cnt,
p_data =>
l_data,
p_encoded =>
fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);
DBMS_OUTPUT.put_line (‘Record = ‘ ||
l_loop_cnt);
DBMS_OUTPUT.put_line (l_data);
IF l_status = ‘E’ OR
l_status = ‘U’
THEN
l_data := CONCAT (‘ERROR ‘, l_data);
END IF;
DBMS_OUTPUT.put_line
(l_data);
IF (l_status = ‘U’)
THEN
l_return_status := l_status;
ELSIF (l_status = ‘E’ AND l_return_status
<> ‘U’)
THEN
l_return_status := l_status;
ELSE
l_return_status := l_status;
END IF;
l_loop_cnt := l_loop_cnt +
1;
IF l_loop_cnt > l_count
THEN
EXIT;
END IF;
END LOOP;
END IF;
COMMIT;
END;
R12 – OPM FORMULA CREATION
USING API
API Material:
DECLARE
CURSOR c_master IS
SELECT *
FROM nfi_gmd_recipe
WHERE flag = ‘V’
AND routing_no IS NOT NULL;
CURSOR c_formula (
p_formula_id number
)
IS
SELECT *
FROM FM_MATL_DTL
WHERE formula_id = p_formula_id
ORDER BY line_type, line_no;
l_recipe_flex_tbl
gmd_recipe_detail.recipe_flex;
l_recipe_mtl_tbl
gmd_recipe_detail.recipe_mtl_tbl;
l_recipe
nfi_gmd_recipe%ROWTYPE;
l_formula
FM_MATL_DTL%ROWTYPE;
l_count NUMBER
:= 0;
l_count_mtl
NUMBER
:= 0;
l_loop_cnt NUMBER
:=
0;
l_record_count NUMBER
:= 0;
l_data
VARCHAR2 (2000);
l_data_mtl
VARCHAR2 (2000);
l_return_status VARCHAR2 (1);
l_status VARCHAR2
(1);
i
NUMBER
:= 1;
l_dummy_cnt NUMBER;
l_api_version NUMBER
:= 1;
l_init_msg_list BOOLEAN;
l_commit BOOLEAN;
return_sts BOOLEAN;
v_routing_id number;
v_formula_id number;
v_routingstep_id number;
v_formulaline_id number;
BEGIN
–FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
fnd_global.apps_initialize (user_id
=> 1090,
resp_id
=> 50618,
resp_appl_id => 552
);
l_init_msg_list := TRUE;
l_commit := TRUE;
i := i;
— IF NOT c_master%ISOPEN THEN
— CLOSE c_master;
— END IF;
OPEN c_master;
LOOP
FETCH c_master INTO l_recipe;
exit WHEN c_master%NOTFOUND;
IF l_recipe.routing_no IS NOT NULL THEN
SELECT routing_id
INTO v_routing_id
FROM fm_rout_hdr
WHERE routing_no = l_recipe.routing_no
AND routing_vers =
l_recipe.routing_vers;
END IF;
SELECT formula_id
INTO v_formula_id
FROM FM_FORM_MST
WHERE formula_no = l_recipe.formula_no
AND formula_vers = l_recipe.formula_vers;
IF l_recipe.routing_no IS NOT NULL THEN
OPEN c_formula(v_formula_id);
LOOP
FETCH c_formula INTO l_formula;
exit WHEN c_formula%NOTFOUND;
IF l_formula.line_type = -1 AND
l_formula.line_no = 1 THEN
SELECT routingstep_id
INTO
v_routingstep_id
FROM fm_rout_dtl
WHERE routing_id =
v_routing_id
AND
routingstep_no = 10;
ELSIF l_formula.line_type = -1
AND l_formula.line_no > 1 THEN
SELECT routingstep_id
INTO
v_routingstep_id
FROM fm_rout_dtl
WHERE routing_id =
v_routing_id
AND
routingstep_no = 20;
ELSIF l_formula.line_type = 1
THEN
SELECT routingstep_id
INTO
v_routingstep_id
FROM fm_rout_dtl
WHERE routing_id =
v_routing_id
AND
routingstep_no = 30;
END IF;
l_recipe_mtl_tbl (i).recipe_no :=
l_recipe.recipe_no;
l_recipe_mtl_tbl (i).recipe_version
:= l_recipe.recipe_version;
l_recipe_mtl_tbl (i).formulaline_id
:= l_formula.formulaline_id;
l_recipe_mtl_tbl (i).routingstep_id
:= v_routingstep_id;
DBMS_OUTPUT.put_line(i);
DBMS_OUTPUT.put_line(l_recipe_mtl_tbl
(i).formulaline_id);
DBMS_OUTPUT.put_line(l_recipe_mtl_tbl
(i).recipe_no);
i := i + 1;
–DBMS_OUTPUT.put_line (‘Value Test ‘
|| i || ‘: ‘|| l_recipe_mtl_tbl (i).recipe_no);
END LOOP;
CLOSE c_formula;
END IF;
UPDATE nfi_gmd_recipe
set flag = ‘M’
WHERE recipe_no = l_recipe.recipe_no;
END LOOP;
CLOSE c_master;
gmd_recipe_detail.create_recipe_mtl
(p_api_version
=> 1.0,
–p_init_msg_list
=> FND_API.G_TRUE,
–p_commit
=> FND_API.G_TRUE,
p_called_from_forms
=> ‘NO’,
x_return_status
=> l_return_status,
x_msg_count
=> l_count_mtl,
x_msg_data
=> l_data_mtl,
p_recipe_mtl_tbl
=> l_recipe_mtl_tbl,
p_recipe_mtl_flex
=> l_recipe_flex_tbl
);
IF l_count_mtl >= 0
THEN
l_loop_cnt := 1;
LOOP
fnd_msg_pub.get (p_msg_index
=> l_loop_cnt,
p_data =>
l_data_mtl,
p_encoded =>
fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);
DBMS_OUTPUT.put_line (‘Record = ‘ ||
l_loop_cnt);
DBMS_OUTPUT.put_line (l_data_mtl);
IF l_status = ‘E’ OR
l_status = ‘U’
THEN
l_data_mtl := CONCAT (‘ERROR ‘,
l_data_mtl);
END IF;
DBMS_OUTPUT.put_line
(l_data_mtl);
IF (l_status = ‘U’)
THEN
l_return_status := l_status;
ELSIF (l_status = ‘E’ AND l_return_status
<> ‘U’)
THEN
l_return_status := l_status;
ELSE
l_return_status := l_status;
END IF;
l_loop_cnt := l_loop_cnt +
1;
IF l_loop_cnt >
l_count_mtl
THEN
EXIT;
END IF;
END LOOP;
END IF;
COMMIT;
END;
API Validity Rule:
DECLARE
CURSOR c_master IS
SELECT *
FROM nfi_gmd_recipe
WHERE flag = ‘Y’;
l_recipe_flex_tbl gmd_recipe_detail.recipe_flex;
l_recipe_vr_tbl
gmd_recipe_detail.recipe_vr_tbl;
l_recipe
nfi_gmd_recipe%ROWTYPE;
l_formula
FM_MATL_DTL%ROWTYPE;
l_count NUMBER
:= 0;
l_count_mtl
NUMBER
:= 0;
l_loop_cnt NUMBER
:=
0;
l_record_count NUMBER
:= 0;
l_data
VARCHAR2 (2000);
l_data_mtl
VARCHAR2 (2000);
l_return_status VARCHAR2 (1);
l_status VARCHAR2
(1);
i
NUMBER
:= 1;
l_dummy_cnt NUMBER;
l_api_version NUMBER
:= 1;
l_init_msg_list BOOLEAN;
l_commit BOOLEAN;
return_sts BOOLEAN;
v_routing_id number;
v_formula_id number;
v_routingstep_id number;
v_formulaline_id number;
BEGIN
–FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
fnd_global.apps_initialize (user_id
=> 1090,
resp_id
=> 50618,
resp_appl_id => 552
);
l_init_msg_list := TRUE;
l_commit := TRUE;
i := 0;
DBMS_OUTPUT.put_line (‘Open cursor’);
OPEN c_master;
LOOP
FETCH c_master INTO l_recipe;
exit WHEN c_master%NOTFOUND;
l_recipe_vr_tbl (c_master%ROWCOUNT).recipe_no :=
l_recipe.recipe_no;
l_recipe_vr_tbl (c_master%ROWCOUNT).recipe_version :=
l_recipe.recipe_version;
l_recipe_vr_tbl (c_master%ROWCOUNT).organization_id :=
l_recipe.organization_id;
l_recipe_vr_tbl (c_master%ROWCOUNT).start_date :=
l_recipe.start_date;
DBMS_OUTPUT.put_line (‘Value Test ‘ || c_master%ROWCOUNT
|| ‘: ‘|| l_recipe_vr_tbl (c_master%ROWCOUNT).recipe_no);
UPDATE nfi_gmd_recipe
set flag = ‘V’
WHERE recipe_no = l_recipe.recipe_no;
END LOOP;
CLOSE c_master;
DBMS_OUTPUT.put_line (‘Close cursor’);
gmd_recipe_detail.create_recipe_vr
(p_api_version
=> 1.0,
p_init_msg_list
=> fnd_api.g_true,
p_commit
=> fnd_api.g_true,
p_called_from_forms
=> ‘NO’,
x_return_status
=> l_return_status,
x_msg_count
=> l_count,
x_msg_data
=> l_data,
p_recipe_vr_tbl
=> l_recipe_vr_tbl,
p_recipe_vr_flex
=> l_recipe_flex_tbl
);
UPDATE GMD_RECIPE_VALIDITY_RULES
set VALIDITY_RULE_STATUS = 700
WHERE VALIDITY_RULE_STATUS != 700;
IF l_count >= 0
THEN
l_loop_cnt := 1;
LOOP
fnd_msg_pub.get (p_msg_index
=> l_loop_cnt,
p_data =>
l_data,
p_encoded =>
fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);
DBMS_OUTPUT.put_line (‘Record = ‘ ||
l_loop_cnt);
DBMS_OUTPUT.put_line (l_data);
IF l_status = ‘E’ OR
l_status = ‘U’
THEN
l_data := CONCAT (‘ERROR ‘, l_data);
END IF;
DBMS_OUTPUT.put_line
(l_data);
IF (l_status = ‘U’)
THEN
l_return_status := l_status;
ELSIF (l_status = ‘E’ AND l_return_status
<> ‘U’)
THEN
l_return_status := l_status;
ELSE
l_return_status := l_status;
END IF;
l_loop_cnt := l_loop_cnt +
1;
IF l_loop_cnt > l_count
THEN
EXIT;
END IF;
END LOOP;
END IF;
COMMIT;
END;
API Recipe Header:
DECLARE
CURSOR c_master IS
SELECT *
FROM nfi_gmd_recipe
WHERE flag IS NULL;
l_recipe_header_tbl gmd_recipe_header.recipe_tbl;
l_recipe_flex_tbl gmd_recipe_header.recipe_flex;
l_recipe nfi_gmd_recipe%ROWTYPE;
l_formula FM_MATL_DTL%ROWTYPE;
l_count NUMBER := 0;
l_count_mtl NUMBER := 0;
l_loop_cnt NUMBER := 0;
l_record_count NUMBER := 0;
l_data VARCHAR2 (2000);
l_data_mtl VARCHAR2 (2000);
l_return_status VARCHAR2 (1);
l_status VARCHAR2 (1);
i NUMBER := 1;
l_dummy_cnt NUMBER;
l_api_version NUMBER := 1;
l_init_msg_list BOOLEAN;
l_commit BOOLEAN;
return_sts BOOLEAN;
v_routing_id number;
v_formula_id number;
v_routingstep_id number;
v_formulaline_id number;
BEGIN
–FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
fnd_global.apps_initialize (user_id => 1090,
resp_id => 50618,
resp_appl_id => 552
);
l_init_msg_list := TRUE;
l_commit := TRUE;
DBMS_OUTPUT.put_line (‘Open cursor’);
OPEN c_master;
LOOP
FETCH c_master INTO l_recipe;
exit WHEN c_master%NOTFOUND;
IF l_recipe.routing_no IS NOT NULL THEN
SELECT routing_id
INTO v_routing_id
FROM fm_rout_hdr
WHERE routing_no = l_recipe.routing_no
AND routing_vers = l_recipe.routing_vers;
l_recipe_header_tbl (c_master%ROWCOUNT).routing_id :=
v_routing_id;
END IF;
SELECT formula_id
INTO v_formula_id
FROM FM_FORM_MST
WHERE formula_no = l_recipe.formula_no
AND formula_vers = l_recipe.formula_vers;
l_recipe_header_tbl (c_master%ROWCOUNT).recipe_no :=
l_recipe.recipe_no;
l_recipe_header_tbl (c_master%ROWCOUNT).recipe_description :=
l_recipe.recipe_description;
l_recipe_header_tbl (c_master%ROWCOUNT).recipe_version :=
l_recipe.recipe_version;
l_recipe_header_tbl (c_master%ROWCOUNT).user_id := 1090;
l_recipe_header_tbl (c_master%ROWCOUNT).owner_organization_id :=
l_recipe.owner_organization_id;
l_recipe_header_tbl (c_master%ROWCOUNT).formula_id := v_formula_id;
l_recipe_header_tbl (c_master%ROWCOUNT).recipe_status :=
‘700’;
DBMS_OUTPUT.put_line (‘Value Test ‘ || c_master%ROWCOUNT ||
‘: ‘|| l_recipe_header_tbl (c_master%ROWCOUNT).recipe_no);
UPDATE nfi_gmd_recipe
set flag = ‘Y’
WHERE recipe_no = l_recipe.recipe_no;
END LOOP;
CLOSE c_master;
DBMS_OUTPUT.put_line (‘Close cursor’);
gmd_recipe_header.create_recipe_header
(p_api_version => 1.0,
–p_init_msg_list => FND_API.G_TRUE,
–p_commit => FND_API.G_TRUE,
p_recipe_header_tbl => l_recipe_header_tbl,
p_recipe_header_flex => l_recipe_flex_tbl,
x_return_status => l_return_status,
x_msg_count => l_count,
x_msg_data => l_data
);
IF l_count >= 0
THEN
l_loop_cnt := 1;
LOOP
fnd_msg_pub.get (p_msg_index => l_loop_cnt,
p_data => l_data,
p_encoded => fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);
DBMS_OUTPUT.put_line (‘Record = ‘ || l_loop_cnt);
DBMS_OUTPUT.put_line (l_data);
IF l_status = ‘E’ OR l_status = ‘U’
THEN
l_data := CONCAT (‘ERROR ‘, l_data);
END IF;
DBMS_OUTPUT.put_line (l_data);
IF (l_status = ‘U’)
THEN
l_return_status := l_status;
ELSIF (l_status = ‘E’ AND l_return_status <> ‘U’)
THEN
l_return_status := l_status;
ELSE
l_return_status := l_status;
END IF;
l_loop_cnt := l_loop_cnt + 1;
IF l_loop_cnt > l_count
THEN
EXIT;
END IF;
END LOOP;
END IF;
COMMIT;
END;
API Formula:
DECLARE
CURSOR c_header IS
SELECT DISTINCT formula_no
FROM mii_gmd_formula
WHERE flag IS NULL;
CURSOR c_master (
p_formula varchar2
) IS
SELECT *
FROM mii_gmd_formula
WHERE flag IS NULL
AND formula_no = p_formula;
l_formula_header_tbl
gmd_formula_pub.formula_insert_hdr_tbl_type;
l_formula
mii_gmd_formula%ROWTYPE;
l_count
NUMBER
:= 0;
l_loop_cnt NUMBER
:= 0;
l_record_count NUMBER
:= 0;
l_data
VARCHAR2 (2000);
l_return_status VARCHAR2 (1);
l_status VARCHAR2
(1);
i
NUMBER
:=
1;
l_dummy_cnt NUMBER;
l_api_version NUMBER
:= 1;
l_init_msg_list BOOLEAN;
l_commit BOOLEAN;
return_sts BOOLEAN;
v_item_id number;
v_organization_id number;
v_user_id
number;
BEGIN
–FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
fnd_global.apps_initialize (user_id
=> 1090,
resp_id
=> 22882,
resp_appl_id => 552
);
/*FND_GLOBAL.APPS_INITIALIZE(user_id, resp_id, resp_appl_id);*/
l_init_msg_list := TRUE;
l_commit := TRUE;
FOR l_header IN c_header
LOOP
OPEN c_master(l_header.formula_no);
LOOP
FETCH c_master INTO l_formula;
exit WHEN c_master%NOTFOUND;
dbms_output.put_line (c_master%ROWCOUNT);
BEGIN
SELECT DISTINCT inventory_item_id
INTO v_item_id
FROM mtl_system_items_b
WHERE segment1 =
UPPER(l_formula.inventory_item_code);
SELECT organization_id
INTO v_organization_id
FROM mtl_parameters
WHERE organization_code = l_formula.owner_organization_code;
SELECT user_id
INTO v_user_id
FROM fnd_user
WHERE user_name = l_formula.owner_name;
EXCEPTION
WHEN others THEN
UPDATE mii_gmd_formula
set flag = ‘E’
–, note = ‘Ada exception’
WHERE formula_no =
l_formula.formula_no;
END;
l_formula_header_tbl (c_master%ROWCOUNT).record_type :=
‘I’;
l_formula_header_tbl (c_master%ROWCOUNT).formula_no :=
UPPER(l_formula.formula_no);
l_formula_header_tbl (c_master%ROWCOUNT).formula_vers :=
l_formula.formula_vers;
l_formula_header_tbl (c_master%ROWCOUNT).formula_type :=
l_formula.formula_type;
l_formula_header_tbl (c_master%ROWCOUNT).formula_desc1 :=
l_formula.formula_desc1;
l_formula_header_tbl (c_master%ROWCOUNT).formula_class :=
l_formula.formula_class;
l_formula_header_tbl (c_master%ROWCOUNT).inactive_ind :=
l_formula.inactive_ind;
l_formula_header_tbl
(c_master%ROWCOUNT).owner_organization_id := v_organization_id;
l_formula_header_tbl (c_master%ROWCOUNT).formula_status :=
l_formula.formula_status;
l_formula_header_tbl (c_master%ROWCOUNT).owner_id :=
v_user_id;
l_formula_header_tbl (c_master%ROWCOUNT).line_type :=
l_formula.line_type;
l_formula_header_tbl (c_master%ROWCOUNT).line_no :=
l_formula.line_no;
l_formula_header_tbl (c_master%ROWCOUNT).inventory_item_id
:= v_item_id;
l_formula_header_tbl (c_master%ROWCOUNT).qty :=
l_formula.qty;
l_formula_header_tbl (c_master%ROWCOUNT).detail_uom :=
l_formula.detail_uom;
l_formula_header_tbl (c_master%ROWCOUNT).release_type :=
l_formula.release_type;
l_formula_header_tbl (c_master%ROWCOUNT).scale_type_hdr :=
l_formula.scale_type_hdr;
l_formula_header_tbl (c_master%ROWCOUNT).scale_type_dtl :=
l_formula.scale_type_dtl;
l_formula_header_tbl (c_master%ROWCOUNT).cost_alloc :=
l_formula.cost_alloc;
l_formula_header_tbl
(c_master%ROWCOUNT).CONTRIBUTE_YIELD_IND := l_formula.CONTRIBUTE_YIELD_IND;
l_formula_header_tbl (c_master%ROWCOUNT).PHANTOM_TYPE :=
l_formula.PHANTOM_TYPE;
l_formula_header_tbl (c_master%ROWCOUNT).delete_mark :=
l_formula.delete_mark;
l_formula_header_tbl
(c_master%ROWCOUNT).CONTRIBUTE_STEP_QTY_IND := ‘N’;
DBMS_OUTPUT.put_line (‘Value Test ‘ ||
l_formula_header_tbl (1).formula_no);
— UPDATE mii_gmd_formula
— set flag = ‘Y’
— WHERE formula_no = l_formula.formula_no
— AND line_no = l_formula.line_no
— AND inventory_item_code =
l_formula.inventory_item_code;
END LOOP;
CLOSE c_master;
gmd_formula_pub.insert_formula
(p_api_version
=> 1.0,
p_formula_header_tbl
=> l_formula_header_tbl,
x_return_status
=> l_return_status,
x_msg_count
=> l_count,
x_msg_data
=> l_data
);
DBMS_OUTPUT.put_line
(‘l_return_status:’||l_return_status);
DBMS_OUTPUT.put_line (‘l_data:’||l_data);
–IF l_return_status = ‘E’ OR l_return_status = ‘U’
— THEN
UPDATE mii_gmd_formula
set flag = l_return_status
–, note = l_data
WHERE formula_no = l_formula.formula_no;
— ELSE
— UPDATE mii_gmd_formula
— set flag = ‘Y’
— , note = l_data
— WHERE formula_no = l_formula.formula_no;
— END IF;
END LOOP;
/*
IF l_count >= 0
THEN
l_loop_cnt := 1;
LOOP
fnd_msg_pub.get (p_msg_index
=> l_loop_cnt,
p_data =>
l_data,
p_encoded =>
fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);
DBMS_OUTPUT.put_line (‘Record = ‘ ||
l_loop_cnt);
DBMS_OUTPUT.put_line (l_data);
IF l_status = ‘E’ OR
l_status = ‘U’
THEN
l_data := CONCAT (‘ERROR ‘, l_data);
END IF;
DBMS_OUTPUT.put_line
(l_data);
IF (l_status = ‘U’)
THEN
l_return_status := l_status;
ELSIF (l_status = ‘E’ AND l_return_status
<> ‘U’)
THEN
l_return_status := l_status;
ELSE
l_return_status := l_status;
END IF;
l_loop_cnt := l_loop_cnt +
1;
IF l_loop_cnt > l_count
THEN
EXIT;
END IF;
END LOOP;
END IF;*/
COMMIT;
END;
OPM RECIPE HEADER UPLOADING
THROUGH SCRIPT USING API
Recipe Header Uploading can be done
using the following steps:
1. First Create a staging table say “MJIL_RCP_HDR_UPL_TBL”
as shown below.
CREATE TABLE MJIL_RCP_HDR_UPL_TBL
(
RECIPE_ID
NUMBER (15),
RECIPE_DESCRIPTION VARCHAR2 (70 BYTE),
RECIPE_NO
VARCHAR2 (32 BYTE),
RECIPE_VERSION NUMBER
(5),
USER_ID
NUMBER (15),
USER_NAME
VARCHAR2 (70 BYTE),
OWNER_ORGN_CODE VARCHAR2
(4 BYTE),
CREATION_ORGN_CODE VARCHAR2 (4 BYTE),
OWNER_ORGANIZATION_ID NUMBER,
CREATION_ORGANIZATION_ID NUMBER,
FORMULA_ID
NUMBER (15),
FORMULA_NO
VARCHAR2 (32 BYTE),
FORMULA_VERS
NUMBER,
ROUTING_ID
NUMBER,
ROUTING_NO
VARCHAR2 (32 BYTE),
ROUTING_VERS
NUMBER (5),
PROJECT_ID
NUMBER (15),
RECIPE_STATUS
VARCHAR2 (30 BYTE),
PLANNED_PROCESS_LOSS NUMBER,
TEXT_CODE
NUMBER (10),
DELETE_MARK
NUMBER (5),
CONTIGUOUS_IND NUMBER,
ENHANCED_PI_IND VARCHAR2
(1 BYTE),
RECIPE_TYPE
NUMBER,
CREATION_DATE
DATE,
CREATED_BY
NUMBER (15),
LAST_UPDATED_BY NUMBER
(15),
LAST_UPDATE_DATE DATE,
LAST_UPDATE_LOGIN NUMBER (15),
OWNER_ID
NUMBER (15),
OWNER_LAB_TYPE VARCHAR2
(4 BYTE),
CALCULATE_STEP_QUANTITY NUMBER (5),
FIXED_PROCESS_LOSS NUMBER,
FIXED_PROCESS_LOSS_UOM VARCHAR2 (3 BYTE),
ATTRIBUTE_CATEGORY VARCHAR2 (30 BYTE),
ATTRIBUTE1
VARCHAR2 (240 BYTE),
ATTRIBUTE2
VARCHAR2 (240 BYTE),
ATTRIBUTE3
VARCHAR2 (240 BYTE),
ATTRIBUTE4
VARCHAR2 (240 BYTE),
ATTRIBUTE5
VARCHAR2 (240 BYTE),
ATTRIBUTE6
VARCHAR2 (240 BYTE),
ATTRIBUTE7
VARCHAR2 (240 BYTE),
ATTRIBUTE8
VARCHAR2 (240 BYTE),
ATTRIBUTE9
VARCHAR2 (240 BYTE),
ATTRIBUTE10
VARCHAR2 (240 BYTE),
ATTRIBUTE11
VARCHAR2 (240 BYTE),
ATTRIBUTE12
VARCHAR2 (240 BYTE),
ATTRIBUTE13
VARCHAR2 (240 BYTE),
ATTRIBUTE14
VARCHAR2 (240 BYTE),
ATTRIBUTE15
VARCHAR2 (240 BYTE),
ATTRIBUTE16
VARCHAR2 (240 BYTE),
ATTRIBUTE17
VARCHAR2 (240 BYTE),
ATTRIBUTE18
VARCHAR2 (240 BYTE),
ATTRIBUTE19 VARCHAR2
(240 BYTE),
ATTRIBUTE20
VARCHAR2 (240 BYTE),
ATTRIBUTE21
VARCHAR2 (240 BYTE),
ATTRIBUTE22
VARCHAR2 (240 BYTE),
ATTRIBUTE23
VARCHAR2 (240 BYTE),
ATTRIBUTE24
VARCHAR2 (240 BYTE),
ATTRIBUTE25
VARCHAR2 (240 BYTE),
ATTRIBUTE26
VARCHAR2 (240 BYTE),
ATTRIBUTE27
VARCHAR2 (240 BYTE),
ATTRIBUTE28
VARCHAR2 (240 BYTE),
ATTRIBUTE29
VARCHAR2 (240 BYTE),
ATTRIBUTE30
VARCHAR2 (240 BYTE)
);
2. Next create a procedure using the script as shown below.
CREATE OR REPLACE PROCEDURE CONA_RECIPE_UPLOAD_PD (ERRBUF
OUT VARCHAR2,
RETCODE OUT NUMBER)
IS
/******************************************************************************
NAME: APPS.CONA_RECIPE_UPLOAD_PD
PURPOSE: Recipe Header Uploading
REVISIONS:
Ver Date Author Description
——— ———- ————— ————————————
1.0 8/16/2013 1. Oracle User Created this procedure.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: APPS.CONA_RECIPE_UPLOAD_PD
******************************************************************************/
mjil_rcp_hdr_tbl
gmd_recipe_header.recipe_tbl;
mjil_rcp_hdr_flex_tbl gmd_recipe_header.recipe_flex;
X_status
VARCHAR2 (1);
X_msg_cnt
NUMBER;
X_msg_dat
VARCHAR2 (1000);
X_row
NUMBER := 1;
l_user_id
NUMBER := 1114;
l_responsibility_id NUMBER := 22883;
l_out_index
NUMBER;
l_responsibility_app_id NUMBER;
CURSOR c1
IS
SELECT * FROM mjil_rcp_hdr_upl_tbl;
BEGIN
FND_GLOBAL.
APPS_INITIALIZE (l_user_id, l_responsibility_id,
l_responsibility_app_id);
FOR i IN c1
LOOP
mjil_rcp_hdr_tbl (X_row).recipe_no := i.recipe_no;
mjil_rcp_hdr_tbl (X_row).recipe_version :=
i.recipe_version;
mjil_rcp_hdr_tbl (X_row).recipe_description :=
i.recipe_description;
mjil_rcp_hdr_tbl (X_row).RECIPE_STATUS := i.RECIPE_STATUS;
mjil_rcp_hdr_tbl (X_row).RECIPE_TYPE := i.RECIPE_TYPE;
mjil_rcp_hdr_tbl (X_row).formula_no := i.formula_no;
mjil_rcp_hdr_tbl (X_row).formula_vers := i.formula_vers;
mjil_rcp_hdr_tbl (X_row).routing_no := i.routing_no;
mjil_rcp_hdr_tbl (X_row).routing_vers := i.routing_vers;
mjil_rcp_hdr_tbl (X_row).delete_mark :=
i.delete_mark;
mjil_rcp_hdr_tbl (X_row).creation_date := SYSDATE;
mjil_rcp_hdr_tbl (X_row).created_by := i.created_by;
mjil_rcp_hdr_tbl (X_row).last_updated_by :=
i.last_updated_by;
mjil_rcp_hdr_tbl (X_row).last_update_date := SYSDATE;
mjil_rcp_hdr_tbl (X_row).last_update_login := 1114;
mjil_rcp_hdr_tbl (X_row).user_name :=
i.user_name;
mjil_rcp_hdr_tbl (X_row).owner_orgn_code :=
i.owner_orgn_code;
mjil_rcp_hdr_tbl (X_row).OWNER_ORGANIZATION_ID :=
i.owner_organization_id;
mjil_rcp_hdr_tbl (X_row).creation_orgn_code :=
i.creation_orgn_code;
mjil_rcp_hdr_tbl (X_row).owner_id := i.owner_id;
mjil_rcp_hdr_flex_tbl
(X_row).attribute1 := ‘FLEX1’;
X_row := X_row + 1;
END LOOP;
gmd_recipe_header.
create_recipe_header (p_api_version
=> 1,
p_init_msg_list => FND_API.G_TRUE,
p_commit =>
FND_API.G_TRUE,
p_called_from_forms => ‘NO’,
x_return_status => X_status,
x_msg_count =>
X_msg_cnt,
x_msg_data =>
X_msg_dat,
p_recipe_header_tbl => mjil_rcp_hdr_tbl,
p_recipe_header_flex => mjil_rcp_hdr_flex_tbl);
DBMS_OUTPUT.put_line (‘Return status – ‘ ||
X_status);
DBMS_OUTPUT.put_line (‘Message count – ‘ || X_msg_cnt);
FOR i IN 1 .. X_msg_cnt
LOOP
FND_MSG_PUB.get (p_msg_index => i,
p_encoded => ‘F’,
p_data => X_msg_dat,
P_MSG_INDEX_OUT => l_out_index);
DBMS_OUTPUT.PUT_LINE (‘Message Text ‘ || X_msg_dat);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Return status – ‘ || X_status);
DBMS_OUTPUT.put_line (‘Message count – ‘ || X_msg_cnt);
FOR i IN 1 .. X_msg_cnt
LOOP
FND_MSG_PUB.get (p_msg_index
=> i,
p_encoded => ‘F’,
p_data => X_msg_dat,
P_MSG_INDEX_OUT => l_out_index);
DBMS_OUTPUT.PUT_LINE (‘Message Text ‘ ||
X_msg_dat);
END LOOP;
END;
/
3. Finally register the procedure and run it.
OPM – GMD INSERT FORMULA
INGREDIENT IN R12
Module GMD R12.GMD.A.6 (UNP Product Development; Process
Manufacturing Product Development)
The procedure for adding an ingredient in the formula
PROCEDURE insert_formuladetail(
p_formula_id NUMBER ,
p_ingredient_id NUMBER ,
p_new_qty NUMBER ,
p_uom VARCHAR2 DEFAULT NULL,
p_return_status OUT VARCHAR2 ,
p_message OUT VARCHAR2 )
IS
l_api_version CONSTANT NUMBER := 1.0;
l_init_msg_list VARCHAR2 (1) :=
FND_API.G_TRUE ;
l_commit VARCHAR2 (1)
:= FND_API.G_FALSE;
l_called_from_forms VARCHAR2 (10) := ‘NO’;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER ;
l_msg_data VARCHAR2 (400);
L_FORMULA_DETAIL_TBL GMD_FORMULA_DETAIL_PUB.FORMULA_INSERT_DTL_TBL_TYPE;
— l_formula_head fm_form_mst_b%rowtype;
L_LINE_NO NUMBER ;
— i pls_ integer := 1;
no_proc_finish EXCEPTION ;
BEGIN
IF (p_formula_id IS NULL) THEN
p_message := ‘P_formula_id parameter can not
be empty ‘;
raise no_proc_finish;
END IF ;
BEGIN
SELECT *
INTO l_formula_head
FROM fm_form_mst_b fm
WHERE fm.formula_id = p_formula_id;
EXCEPTION
WHEN OTHERS THEN
p_message := ‘ Could not managed to find a formula ‘;
raise no_proc_finish;
END ;
BEGIN
SELECT MAX (fm.line_no)
INTO l_line_no
FROM fm_matl_dtl fm
WHERE fm.FORMULA_ID = p_formula_id
AND fm.line_type = -1;
EXCEPTION
WHEN OTHERS THEN
p_message := ‘ Could not find a string formula ‘;
raise no_proc_finish;
END ;
gme_common_pvt.set_who;
l_formula_detail_tbl(i).formula_id :=
p_formula_id;
l_formula_detail_tbl(i).formula_no :=
l_formula_head.formula_no;
l_formula_detail_tbl(i).formula_vers :=
l_formula_head.formula_vers;
l_formula_detail_tbl(i).line_type :=
-1;
l_formula_detail_tbl(i).line_no :=
l_line_no + 1;
l_formula_detail_tbl(i).inventory_item_id := p_ingredient_id;
l_formula_detail_tbl(i).QTY
:= p_new_qty;
L_FORMULA_DETAIL_TBL(I).RELEASE_TYPE := 1; —
manually
IF (p_uom
IS NOT NULL) THEN
l_formula_detail_tbl(i).DETAIL_UOM := p_uom;
END IF ;
gmd_formula_detail_pub.insert_formuladetail( p_api_version =>
l_api_version ,p_init_msg_list => l_init_msg_list ,p_commit => l_commit
,p_called_from_forms => l_called_from_forms ,x_return_status =>
l_return_status ,x_msg_count => l_msg_count ,x_msg_data => l_msg_data
,p_formula_detail_tbl => l_formula_detail_tbl );
IF l_return_status <> FND_API.g_ret_sts_success THEN
IF l_msg_count = 1 THEN
p_message := FND_MSG_PUB.get(1,’F’);
RAISE no_proc_finish;
ELSE
FOR l IN 1..l_msg_count
LOOP
FND_MSG_PUB.get (p_msg_index => l ,p_encoded
=> ‘ F ‘ ,p_data => p_message ,p_msg_index_out => l_msg_count);
END LOOP ;
RAISE no_proc_finish;
END IF ;
END IF ;
p_return_status := ‘ S ‘;
EXCEPTION
WHEN no_proc_finish THEN
p_return_status := ‘ E ‘;
WHEN OTHERS THEN
p_message := SQLERRM ;
p_return_status := ‘ E ‘;
END ;
OPM FORMULA UPLOADING THROUGH
SCRIPT USING API
Formula Upload can be done using the
following steps:
1. Create a staging table say “MJIL_FORMULA_UPLOAD” whose
structure is given below.
CREATE TABLE MJIL_FORMULA_UPLOAD
(
RECORD_TYPE
VARCHAR2 (1 BYTE),
FORMULA_NO
VARCHAR2 (32 BYTE),
FORMULA_VERS
NUMBER,
FORMULA_TYPE
NUMBER,
FORMULA_DESC1 VARCHAR2
(70 BYTE),
FORMULA_DESC2 VARCHAR2
(70 BYTE),
FORMULA_CLASS VARCHAR2
(32 BYTE),
FMCONTROL_CLASS VARCHAR2 (32
BYTE),
INACTIVE_IND
NUMBER,
OWNER_ORGANIZATION_ID NUMBER,
TOTAL_INPUT_QTY NUMBER,
TOTAL_OUTPUT_QTY NUMBER,
YIELD_UOM
VARCHAR2 (3 BYTE),
FORMULA_STATUS VARCHAR2
(30 BYTE),
OWNER_ID
NUMBER (15),
FORMULA_ID
NUMBER,
FORMULALINE_ID NUMBER,
LINE_TYPE
NUMBER,
LINE_NO
NUMBER,
ITEM_NO
VARCHAR2 (2000 BYTE),
INVENTORY_ITEM_ID NUMBER,
REVISION
VARCHAR2 (3 BYTE),
QTY
NUMBER,
DETAIL_UOM
VARCHAR2 (3 BYTE),
MASTER_FORMULA_ID NUMBER,
RELEASE_TYPE
NUMBER,
SCRAP_FACTOR
NUMBER,
SCALE_TYPE_HDR NUMBER,
SCALE_TYPE_DTL NUMBER,
COST_ALLOC
NUMBER,
PHANTOM_TYPE
NUMBER,
REWORK_TYPE
NUMBER,
BUFFER_IND
NUMBER,
BY_PRODUCT_TYPE VARCHAR2 (1
BYTE),
INGREDIENT_END_DATE DATE,
ATTRIBUTE1
VARCHAR2 (240 BYTE),
ATTRIBUTE2
VARCHAR2 (240 BYTE),
ATTRIBUTE3
VARCHAR2 (240 BYTE),
ATTRIBUTE4
VARCHAR2 (240 BYTE),
ATTRIBUTE5
VARCHAR2 (240 BYTE),
ATTRIBUTE6
VARCHAR2 (240 BYTE),
ATTRIBUTE7
VARCHAR2 (240 BYTE),
ATTRIBUTE8
VARCHAR2 (240 BYTE),
ATTRIBUTE9
VARCHAR2 (240 BYTE),
ATTRIBUTE10
VARCHAR2 (240 BYTE),
ATTRIBUTE11
VARCHAR2 (240 BYTE),
ATTRIBUTE12
VARCHAR2 (240 BYTE),
ATTRIBUTE13
VARCHAR2 (240 BYTE),
ATTRIBUTE14
VARCHAR2 (240 BYTE),
ATTRIBUTE15
VARCHAR2 (240 BYTE),
ATTRIBUTE16
VARCHAR2 (240 BYTE),
ATTRIBUTE17
VARCHAR2 (240 BYTE),
ATTRIBUTE18
VARCHAR2 (240 BYTE),
ATTRIBUTE19
VARCHAR2 (240 BYTE),
ATTRIBUTE20
VARCHAR2 (240 BYTE),
ATTRIBUTE21
VARCHAR2 (240 BYTE),
ATTRIBUTE22
VARCHAR2 (240 BYTE),
ATTRIBUTE23
VARCHAR2 (240 BYTE),
ATTRIBUTE24
VARCHAR2 (240 BYTE),
ATTRIBUTE25
VARCHAR2 (240 BYTE),
ATTRIBUTE26
VARCHAR2 (240 BYTE),
ATTRIBUTE27
VARCHAR2 (240 BYTE),
ATTRIBUTE28
VARCHAR2 (240 BYTE),
ATTRIBUTE29
VARCHAR2 (240 BYTE),
ATTRIBUTE30
VARCHAR2 (240 BYTE),
DTL_ATTRIBUTE1 VARCHAR2
(240 BYTE),
DTL_ATTRIBUTE2 VARCHAR2
(240 BYTE),
DTL_ATTRIBUTE3 VARCHAR2
(240 BYTE),
DTL_ATTRIBUTE4 VARCHAR2
(240 BYTE),
DTL_ATTRIBUTE5 VARCHAR2
(240 BYTE),
DTL_ATTRIBUTE6 VARCHAR2
(240 BYTE),
DTL_ATTRIBUTE7 VARCHAR2
(240 BYTE),
DTL_ATTRIBUTE8 VARCHAR2
(240 BYTE),
DTL_ATTRIBUTE9 VARCHAR2
(240 BYTE),
DTL_ATTRIBUTE10 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE11 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE12 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE13 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE14 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE15 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE16 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE17 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE18 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE19 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE20 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE21 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE22 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE23 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE24 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE25 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE26 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE27 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE28 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE29 VARCHAR2 (240
BYTE),
DTL_ATTRIBUTE30 VARCHAR2 (240
BYTE),
ATTRIBUTE_CATEGORY VARCHAR2 (30 BYTE),
DTL_ATTRIBUTE_CATEGORY VARCHAR2 (30 BYTE),
TPFORMULA_ID
NUMBER,
IAFORMULA_ID
NUMBER,
SCALE_MULTIPLE NUMBER,
CONTRIBUTE_YIELD_IND VARCHAR2 (1 BYTE),
SCALE_UOM
VARCHAR2 (4 BYTE),
CONTRIBUTE_STEP_QTY_IND VARCHAR2 (1 BYTE),
SCALE_ROUNDING_VARIANCE NUMBER,
ROUNDING_DIRECTION NUMBER,
TEXT_CODE_HDR NUMBER,
TEXT_CODE_DTL NUMBER,
USER_ID
NUMBER,
CREATION_DATE DATE,
CREATED_BY
NUMBER (15),
LAST_UPDATED_BY NUMBER (15),
LAST_UPDATE_DATE DATE,
LAST_UPDATE_LOGIN NUMBER (15),
USER_NAME
VARCHAR2 (100 BYTE),
DELETE_MARK NUMBER
DEFAULT 0,
AUTO_PRODUCT_CALC VARCHAR2 (1 BYTE),
PROD_PERCENT
NUMBER
);
2. Next create a procedure similar to the one given below.
CREATE OR REPLACE PROCEDURE APPS.CONA_FML_UPLOAD_PD (ERRBUF
OUT VARCHAR2,
RETCODE OUT NUMBER)
IS
/******************************************************************************
NAME: CONA_FML_UPLOAD_PD
PURPOSE: Formula Uploading
REVISIONS:
Ver Date Author Description
——— ———- ————— ————————————
1.0 8/16/2013 1. Oracle User Created this procedure.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: CONA_FML_UPLOAD_PD
******************************************************************************/
mjil_fml_tabtype
apps.gmd_formula_pub.formula_insert_hdr_tbl_type;
CURSOR c1
IS
SELECT *
FROM MJIL_FORMULA_UPLOAD
WHERE formula_no NOT IN (SELECT formula_no FROM
fm_form_mst);
cnt
NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count
NUMBER;
l_msg_data
VARCHAR2 (1000);
l_out_index
NUMBER := 0;
l_user_id
NUMBER := 1114;
l_responsibility_id NUMBER := 22883;
l_responsibility_app_id NUMBER;
BEGIN
FND_GLOBAL.
APPS_INITIALIZE (l_user_id, l_responsibility_id,
l_responsibility_app_id);
cnt := 0;
FOR i IN c1
LOOP
cnt := cnt + 1;
mjil_fml_tabtype (cnt).record_type := i.record_type;
mjil_fml_tabtype (cnt).FORMULA_NO := TRIM (i.FORMULA_NO);
mjil_fml_tabtype (cnt).FORMULA_VERS := i.formula_vers;
mjil_fml_tabtype (cnt).formula_type := i.formula_type;
mjil_fml_tabtype (cnt).formula_desc1 := i.formula_desc1;
mjil_fml_tabtype (cnt).formula_desc2 := i.formula_desc2;
mjil_fml_tabtype (cnt).inactive_ind := i.inactive_ind;
mjil_fml_tabtype (cnt).OWNER_ORGANIZATION_ID :=
i.owner_organization_id;
mjil_fml_tabtype (cnt).total_input_qty := i.total_input_qty;
mjil_fml_tabtype (cnt).total_output_qty :=
i.total_output_qty;
mjil_fml_tabtype (cnt).formula_status := i.formula_status;
mjil_fml_tabtype (cnt).line_no := i.line_no;
mjil_fml_tabtype (cnt).line_type := i.line_type;
mjil_fml_tabtype (cnt).item_no := i.item_no;
mjil_fml_tabtype (cnt).qty := i.qty;
mjil_fml_tabtype (cnt).detail_uom := i.detail_uom;
mjil_fml_tabtype (cnt).release_type := i.release_Type;
mjil_fml_tabtype (cnt).scrap_factor := i.scrap_factor;
mjil_fml_tabtype (cnt).scale_type_hdr := i.scale_type_hdr;
mjil_fml_tabtype (cnt).scale_type_dtl := i.scale_type_dtl;
mjil_fml_tabtype (cnt).cost_alloc := i.cost_alloc;
mjil_fml_tabtype (cnt).phantom_type := i.phantom_type;
mjil_fml_tabtype (cnt).rework_type := i.rework_type;
mjil_fml_tabtype (cnt).buffer_ind := i.buffer_ind;
mjil_fml_tabtype (cnt).contribute_yield_ind :=
i.contribute_yield_ind;
mjil_fml_tabtype (cnt).contribute_step_qty_ind :=
i.contribute_step_qty_ind;
mjil_fml_tabtype (cnt).delete_mark := i.delete_mark;
END LOOP;
GMD_FORMULA_PUB.Insert_Formula (p_api_version
=> 1,
p_init_msg_list
=> FND_API.G_TRUE,
p_commit
=> FND_API.G_TRUE,
p_called_from_forms
=> ‘NO’,
x_return_status
=> l_return_status,
x_msg_count
=> l_msg_count,
x_msg_data
=> l_msg_data,
p_formula_header_tbl
=> mjil_fml_tabtype);
DBMS_OUTPUT.put_line (‘Return status – ‘ || l_return_status);
DBMS_OUTPUT.put_line (‘Message count – ‘ || l_msg_count);
FOR i IN 1 .. l_msg_count
LOOP
FND_MSG_PUB.get (p_msg_index => i,
p_encoded => ‘F’,
p_data => l_msg_data,
P_MSG_INDEX_OUT
=> l_out_index);
DBMS_OUTPUT.PUT_LINE (‘Message Text ‘ || l_msg_data);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Return status – ‘ ||
l_return_status);
DBMS_OUTPUT.put_line (‘Message count – ‘ || l_msg_count);
FOR i IN 1 .. l_msg_count
LOOP
FND_MSG_PUB.get (p_msg_index
=> i,
p_encoded => ‘F’,
p_data => l_msg_data,
P_MSG_INDEX_OUT => l_out_index);
DBMS_OUTPUT.PUT_LINE (‘Message Text ‘ ||
l_msg_data);
END LOOP;
END;
3. Register and run the procedure in Oracle Apps.
OPM – GMD – DELETE ROW FROM THE
FORMULA IN R12
Module GMD R12.GMD.A.6 (UNP
Product Development; Process Manufacturing Product Development)
The
procedure to delete the row from the formula
PROCEDURE
delete_formuladetail(
p_formula_id NUMBER ,
p_formulaline_id NUMBER ,
p_return_status OUT VARCHAR2 ,
p_message OUT VARCHAR2 )
IS
l_api_version CONSTANT NUMBER := 1.1;
l_init_msg_list VARCHAR2(1) :=
FND_API.G_TRUE ;
l_commit VARCHAR2(1)
:= FND_API.G_FALSE;
l_called_from_forms VARCHAR2(10) := ‘NO’;
l_return_status VARCHAR2(1) ;
l_msg_count NUMBER ;
l_msg_data VARCHAR2(400) ;
L_FORMULA_DETAIL_TBL GMD_FORMULA_DETAIL_PUB.FORMULA_UPDATE_DTL_TBL_TYPE;
— l_formula_head fm_form_mst_b%rowtype;
L_FORMULA_LINE FM_MATL_DTL%ROWTYPE;
— i pls_integer := 1;
NO_PROC_FINISH EXCEPTION ;
BEGIN
IF (P_FORMULA_ID IS NULL) THEN
P_MESSAGE := ‘ P_formula_id parameter can not
be empty ‘;
RAISE NO_PROC_FINISH;
END IF ;
— получаем данные заголовка
BEGIN
SELECT *
INTO l_formula_head
FROM fm_form_mst_b fm
WHERE fm.formula_id = p_formula_id;
EXCEPTION
WHEN OTHERS THEN
p_message := ‘ Unable to find a formula ‘;
raise no_proc_finish;
END ;
— get the data string formula
BEGIN
SELECT *
INTO l_formula_line
FROM fm_matl_dtl fm
WHERE fm.formulaline_id = p_formulaline_id;
EXCEPTION
WHEN OTHERS THEN
p_message := ‘ Could not find a string formula ‘;
raise no_proc_finish;
END ;
gme_common_pvt.set_who;
l_formula_detail_tbl(i).RECORD_TYPE := ‘D’;
l_formula_detail_tbl(i).formula_id := p_formula_id;
l_formula_detail_tbl(i).formula_no := l_formula_head.formula_no;
l_formula_detail_tbl(i).formula_vers :=
l_formula_head.formula_vers;
l_formula_detail_tbl(i).formulaline_id := p_formulaline_id;
GMD_FORMULA_DETAIL_PUB.DELETE_FORMULADETAIL( P_API_VERSION =>
L_API_VERSION, P_INIT_MSG_LIST => L_INIT_MSG_LIST, P_COMMIT => L_COMMIT,
P_CALLED_FROM_FORMS => L_CALLED_FROM_FORMS, X_RETURN_STATUS =>
L_RETURN_STATUS, X_MSG_COUNT => L_MSG_COUNT, X_MSG_DATA => L_MSG_DATA,
P_FORMULA_DETAIL_TBL => L_FORMULA_DETAIL_TBL );
IF l_return_status != FND_API.g_ret_sts_success THEN
IF l_msg_count = 1 THEN
p_message := FND_MSG_PUB.get(1,’F’);
RAISE no_proc_finish;
ELSE
FOR l IN 1..l_msg_count
LOOP
FND_MSG_PUB.get (p_msg_index => l ,p_encoded
=> ‘F’ ,p_data => p_message ,p_msg_index_out => l_msg_count);
END LOOP ;
RAISE no_proc_finish;
END IF ;
END IF ;
p_return_status := ‘S’;
EXCEPTION
WHEN no_proc_finish THEN
p_return_status := ‘ E ‘;
WHEN OTHERS THEN
p_message := SQLERRM ;
p_return_status := ‘ E ‘;
END ;
=======================MY
RRRFFFLLLLLLL========================
CREATE
TABLE XXCUST.XXJLN_FORMULA_HEADERS_REZA
(
FORMULA_NO VARCHAR2(32 BYTE) NOT NULL,
FORMULA_VERS NUMBER DEFAULT 1 NOT NULL,
FORMULA_TYPE NUMBER(5) DEFAULT 1 NOT NULL,
FORMULA_DESC1 VARCHAR2(70 BYTE),
FORMULA_CLASS VARCHAR2(8 BYTE),
INACTIVE_IND NUMBER(5) DEFAULT 0 NOT NULL,
FORMULA_UOM VARCHAR2(4 BYTE),
FORMULA_STATUS VARCHAR2(30 BYTE) DEFAULT 100 NOT NULL,
FLAG VARCHAR2(2 BYTE),
RECORD_TYPE VARCHAR2(12 BYTE),
OWNER_ORGANIZATION_CODE VARCHAR2(10 BYTE) DEFAULT 'FMO' NOT NULL,
OWNER_NAME VARCHAR2(20 BYTE) DEFAULT '34220' NOT NULL,
LINE_TYPE NUMBER(10),
LINE_NO INTEGER DEFAULT 1 NOT NULL,
QTY NUMBER(20,10),
RELEASE_TYPE VARCHAR2(10 BYTE),
COST_ALLOC VARCHAR2(10 BYTE),
SCALE_TYPE_HDR NUMBER(10) DEFAULT 1 NOT NULL,
DETAIL_UOM VARCHAR2(10 BYTE),
SCALE_TYPE_DTL INTEGER DEFAULT 1 NOT NULL,
PHANTOM_TYPE INTEGER DEFAULT 0 NOT NULL,
DELETE_MARK VARCHAR2(10 BYTE) DEFAULT 0 NOT NULL,
CONTRIBUTE_YIELD_IND VARCHAR2(10 BYTE) DEFAULT 'Y' NOT NULL,
INVENTORY_ITEM_CODE VARCHAR2(15 BYTE),
TYPE_OF_ITEM VARCHAR2(250 BYTE),
FILE_NAME
VARCHAR2(250 BYTE),
ORG_CODE VARCHAR2(200 BYTE),
ORG_NAME VARCHAR2(300 BYTE),
NOTE VARCHAR2(250 BYTE)
)
--------------------------------PROCESS-------------------------------------
DECLARE
begin
/*
----------First
time for Item Assignmet-------------
delete
from APPS.xxpran_subinv_locator_stg
where
USER_NAME='34220';
commit;
INSERT
INTO
APPS.xxpran_subinv_locator_stg(ITEM_NUMBER,ORG_CODE,SUBINV_CODE,USER_NAME)
SELECT
distinct INVENTORY_ITEM_CODE,'FMO','FMO','34220'
FROM
XXCUST.XXJLN_FORMULA_HEADERS_REZA
where
FLAG is null;
commit;
delete
FROM
APPS.xxpran_subinv_locator_stg WHERE ITEM_NUMBER NOT IN (
SELECT
BB.SEGMENT1 FROM MTL_SYSTEM_ITEMS_B BB WHERE BB.ORGANIZATION_ID=102 AND BB.SEGMENT1
IN (
SELECT
ITEM_NUMBER FROM APPS.xxpran_subinv_locator_stg
));
commit;
apps.xxpran_inv_assign('34220');
*/
----------After
run one time then off this------------------
DECLARE
CURSOR c_header IS
SELECT DISTINCT formula_no
FROM
xxcust.XXJLN_FORMULA_HEADERS_REZA a
WHERE flag IS NULL
and
not exists (select 1 from
fm_form_mst b where a.FORMULA_NO = b.formula_no);
CURSOR c_master (
p_formula varchar2
--TEST_M020
) IS
SELECT *
FROM
xxcust.XXJLN_FORMULA_HEADERS_REZA --mii_gmd_formula
WHERE flag IS NULL
AND formula_no = p_formula;
l_formula_header_tbl gmd_formula_pub.formula_insert_hdr_tbl_type;
l_formula xxcust.XXJLN_FORMULA_HEADERS_REZA%ROWTYPE;
-- mii_gmd_formula%ROWTYPE;
l_count NUMBER := 0;
l_loop_cnt NUMBER := 0;
l_record_count NUMBER := 0;
l_data VARCHAR2 (2000);
l_return_status VARCHAR2 (1);
l_status VARCHAR2 (1);
i NUMBER := 1;
l_dummy_cnt NUMBER;
l_api_version NUMBER := 1;
l_init_msg_list BOOLEAN;
l_commit BOOLEAN;
return_sts BOOLEAN;
v_item_id number;
v_organization_id number;
v_user_id number;
v_total_form number;
BEGIN
--FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
fnd_global.apps_initialize (user_id => 1110,
resp_id => 50774,
resp_appl_id => 555
);
-- FND_RESPONSIBILITY_VL , FND_user
/*FND_GLOBAL.APPS_INITIALIZE(user_id,
resp_id, resp_appl_id);*/
l_init_msg_list := TRUE;
l_commit := TRUE;
FOR
l_header IN c_header
LOOP
OPEN c_master(l_header.formula_no);
LOOP
FETCH c_master INTO l_formula;
exit WHEN c_master%NOTFOUND;
dbms_output.put_line (c_master%ROWCOUNT);
BEGIN
SELECT DISTINCT inventory_item_id
INTO v_item_id
FROM mtl_system_items_b
WHERE segment1 =
UPPER(l_formula.inventory_item_code);
SELECT organization_id
INTO v_organization_id
FROM mtl_parameters
WHERE organization_code =
l_formula.owner_organization_code;
SELECT user_id
INTO v_user_id
FROM fnd_user
WHERE user_name =
l_formula.owner_name;
EXCEPTION
WHEN others THEN
UPDATE
xxcust.XXJLN_FORMULA_HEADERS_REZA --mii_gmd_formula
set flag = 'E'
--, note = 'Ada exception'
WHERE formula_no =
l_formula.formula_no;
END;
l_formula_header_tbl
(c_master%ROWCOUNT).record_type := 'I';
l_formula_header_tbl
(c_master%ROWCOUNT).formula_no := UPPER(l_formula.formula_no);
l_formula_header_tbl
(c_master%ROWCOUNT).formula_vers := l_formula.formula_vers;
l_formula_header_tbl
(c_master%ROWCOUNT).formula_type := l_formula.formula_type;
l_formula_header_tbl
(c_master%ROWCOUNT).formula_desc1 := l_formula.formula_desc1;
l_formula_header_tbl
(c_master%ROWCOUNT).formula_class := l_formula.formula_class;
l_formula_header_tbl
(c_master%ROWCOUNT).inactive_ind := l_formula.inactive_ind;
l_formula_header_tbl
(c_master%ROWCOUNT).owner_organization_id := v_organization_id;
l_formula_header_tbl (c_master%ROWCOUNT).formula_status
:= l_formula.formula_status;
l_formula_header_tbl
(c_master%ROWCOUNT).owner_id := v_user_id;
l_formula_header_tbl
(c_master%ROWCOUNT).line_type := l_formula.line_type;
l_formula_header_tbl
(c_master%ROWCOUNT).line_no := l_formula.line_no;
l_formula_header_tbl
(c_master%ROWCOUNT).inventory_item_id := v_item_id;
l_formula_header_tbl
(c_master%ROWCOUNT).qty := l_formula.qty;
l_formula_header_tbl
(c_master%ROWCOUNT).detail_uom := l_formula.detail_uom;
l_formula_header_tbl
(c_master%ROWCOUNT).release_type := l_formula.release_type;
l_formula_header_tbl
(c_master%ROWCOUNT).scale_type_hdr := l_formula.scale_type_hdr;
l_formula_header_tbl
(c_master%ROWCOUNT).scale_type_dtl := l_formula.scale_type_dtl;
l_formula_header_tbl
(c_master%ROWCOUNT).cost_alloc := l_formula.cost_alloc;
l_formula_header_tbl
(c_master%ROWCOUNT).CONTRIBUTE_YIELD_IND := 'Y'; --
l_formula.CONTRIBUTE_YIELD_IND;
l_formula_header_tbl
(c_master%ROWCOUNT).PHANTOM_TYPE := l_formula.PHANTOM_TYPE;
l_formula_header_tbl
(c_master%ROWCOUNT).delete_mark := l_formula.delete_mark;
l_formula_header_tbl
(c_master%ROWCOUNT).CONTRIBUTE_STEP_QTY_IND := 'Y';
DBMS_OUTPUT.put_line ('Value Test ' ||
l_formula_header_tbl (1).formula_no);
-- UPDATE mii_gmd_formula
-- set flag = 'Y'
-- WHERE formula_no = l_formula.formula_no
-- AND line_no = l_formula.line_no
-- AND inventory_item_code =
l_formula.inventory_item_code;
END LOOP;
CLOSE c_master;
gmd_formula_pub.insert_formula
(p_api_version =>
1.0,
p_formula_header_tbl =>
l_formula_header_tbl,
x_return_status => l_return_status,
x_msg_count =>
l_count,
x_msg_data =>
l_data
);
DBMS_OUTPUT.put_line
('l_return_status:'||l_return_status);
DBMS_OUTPUT.put_line ('l_data:'||l_data);
IF l_return_status = 'E' OR l_return_status
= 'U'
THEN
UPDATE
xxcust.XXJLN_FORMULA_HEADERS_REZA--mii_gmd_formula
set flag = l_return_status
--, note = l_data
WHERE formula_no = l_formula.formula_no;
ELSE
UPDATE
xxcust.XXJLN_FORMULA_HEADERS_REZA--mii_gmd_formula
set flag = 'Y'
-- , note = l_data
WHERE formula_no = l_formula.formula_no;
END IF;
END LOOP;
/*
IF l_count >= 0
THEN
l_loop_cnt := 1;
LOOP
fnd_msg_pub.get (p_msg_index => l_loop_cnt,
p_data => l_data,
p_encoded => fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);
DBMS_OUTPUT.put_line ('Record = ' ||
l_loop_cnt);
DBMS_OUTPUT.put_line (l_data);
IF l_status = 'E' OR l_status = 'U'
THEN
l_data := CONCAT ('ERROR ',
l_data);
END IF;
DBMS_OUTPUT.put_line (l_data);
IF (l_status = 'U')
THEN
l_return_status := l_status;
ELSIF (l_status = 'E' AND
l_return_status <> 'U')
THEN
l_return_status := l_status;
ELSE
l_return_status := l_status;
END IF;
l_loop_cnt := l_loop_cnt + 1;
IF l_loop_cnt > l_count
THEN
EXIT;
END IF;
END LOOP;
END IF;*/
COMMIT;
update
XXCUST.XXJLN_FORMULA_HEADERS_REZA
set FLAG
=null
where
FLAG = 'E';
commit;
---select
count(1) into v_total_form from
XXCUST.XXJLN_FORMULA_HEADERS_REZA a where (a.flag is null or a.flag !='Y');
END;
END;
/
/
---------------------------------------Last
Update---------Process--------------------------------------------
select *
from xxcust.XXJLN_FORMULA_HEADERS_KYS where DATA_NOTE='Y' and
ISTATUS<>'Y' ---and OWNER_ORGANIZATION_CODE='PD1'
select
distinct FORMULA_NO from xxcust.XXJLN_FORMULA_HEADERS_KYS where DATA_NOTE='Y'
and ISTATUS<>'Y'
update
xxcust.XXJLN_FORMULA_HEADERS_KYS
set
ISTATUS='Y'
where
DATA_NOTE='Y' and ISTATUS<>'Y' --
01844661191
select *
from xxcust.XXJLN_FORMULA_HEADERS_KYS where FORMULA_NO='961134'
select *
from xxcust.XXJLN_FORMULA_HEADERS_REZA where FORMULA_NO in ('924143','620019')
--'927617'
select
distinct FORMULA_NO from xxcust.XXJLN_FORMULA_HEADERS_REZA --410
select *
from xxcust.XXJLN_FORMULA_HEADERS_KYS_XL --where FORMULA_NO='804702'
SELECT
* ---count(1) DD ---formula_no
FROM
xxcust.XXJLN_FORMULA_HEADERS_REZA a
WHERE flag IS NULL
and
not exists (
select 1 from fm_form_mst b where a.FORMULA_NO =
b.formula_no and a.FORMULA_VERS=b.FORMULA_VERS
);
insert
into XXCUST.XXJLN_FORMULA_HEADERS_REZA(
FORMULA_NO,
FORMULA_DESC1, LINE_TYPE, ORG_CODE,QTY,
RELEASE_TYPE,PHANTOM_TYPE,
COST_ALLOC, INVENTORY_ITEM_CODE, TYPE_OF_ITEM, NOTE,FM_YES_NO)
SELECT
FORMULA_NO, DESCRIPTION FORMULA_DESC1,decode( TYPE_OF_ITEM,
'INGREDIENT',-1,'PRODUCT',1,'COPRODUCT',1,'BYPRODUCT',2)LINE_TYPE,
OWNER_ORGANIZATION_CODE
ORG_CODE,QTY, decode( TYPE_OF_ITEM,
'INGREDIENT',0,'PRODUCT',1,'COPRODUCT',1,'BYPRODUCT',1)RELEASE_TYPE,
decode(
TYPE_OF_ITEM,
'INGREDIENT',0,'PRODUCT',1,'COPRODUCT',1,'BYPRODUCT',1)PHANTOM_TYPE, COST_ALLOC
,
INVENTORY_ITEM_CODE,
TYPE_OF_ITEM, CREATE_USER NOTE,DATA_NOTE FM_YES_NO
from xxcust.XXJLN_FORMULA_HEADERS_KYS,
mtl_system_items_b
where
FORMULA_NO = SEGMENT1
and
organization_id=102
and
DATA_NOTE='Y'
and
ISTATUS<>'Y'
--and
FORMULA_NO='939360'
;
SELECT
DISTINCT formula_no
FROM
xxcust.XXJLN_FORMULA_HEADERS_REZA a
WHERE flag IS NULL
and
not exists (select 1 from
fm_form_mst b where a.FORMULA_NO = b.formula_no and
a.FORMULA_VERS=b.FORMULA_VERS);
select *
from xxcust.XXJLN_FORMULA_HEADERS_KYS where ISTATUS<>'Y' and FORMULA_NO='992768'
select *
from XXCUST.XXJLN_FORMULA_HEADERS_REZA --where FORMULA_NO='993663'
create
Table
select *
from
XXCUST.XXJLN_FORMULA_HEADERS_REZA_KYS_BKUP
select *
from XXCUST.XXJLN_FORMULA_HEADERS_REZA_bkup where FORMULA_NO='702933'
select *
from fm_form_mst where FORMULA_STATUS =100 --and FORMULA_NO='968781'
select *
from fm_matl_dtl where FORMULA_ID=70831
---update
fm_form_mst set FORMULA_STATUS=700
where FORMULA_STATUS = 100
--------------------------------------------------------------------------
select
a.FORMULA_NO, count(1)
from fm_form_mst a
group by
a.FORMULA_NO ----,a.FORMULA_VERS
having
count(1)>2
-------------Duplicate
Item Ingradient------------
select
a.FORMULA_NO,a.FORMULA_VERS, i(b.INVENTORY_ITEM_ID),a.LAST_UPDATED_BY,a.CREATED_BY,
count(1) DD
from fm_form_mst a , fm_matl_dtl b
where
a.FORMULA_ID = b.FORMULA_ID
--and
a.FORMULA_NO='993663'
and
b.LINE_TYPE= -1
--and
a.CREATED_BY = 2264
---and
a.FORMULA_STATUS='100'
group by
a.FORMULA_NO,a.FORMULA_VERS, b.INVENTORY_ITEM_ID,a.LAST_UPDATED_BY,a.CREATED_BY
having
count(1) > 1
order by
a.FORMULA_NO
select *
from fm_form_mst a where a.FORMULA_STATUS='100'
--update
fm_form_mst a
set
a.FORMULA_STATUS='700'
where
a.FORMULA_STATUS='100'
select i(2688586)from dual 0185050700 - 2688586 -
REJECTION PVC FITTINGS
select *
from GMF_PERIOD_STATUSES P 1225
select *
from gl_item_cst b where b.PERIOD_ID =922 and b.ACCTG_COST < 0 --iteid =213219 org-356
select
i(INVENTORY_ITEM_ID),o(b.ORGANIZATION_ID) from gl_item_cst b where b.PERIOD_ID
=922 and b.ACCTG_COST < 0
select *
from mtl_system_items_b where segment1 = '2688586' and organization_id=102 ---1671878
select *
from org_organization_definitions where organization_code ='PI6' --393 3762
================================================================================================================
DECLARE
begin
----------First
time for Item Assignmet-------------
delete
from APPS.xxpran_subinv_locator_stg
where
USER_NAME='34220';
commit;
INSERT
INTO
APPS.xxpran_subinv_locator_stg(ITEM_NUMBER,ORG_CODE,SUBINV_CODE,USER_NAME)
SELECT
distinct INVENTORY_ITEM_CODE,'FMO','FMO','34220'
FROM
XXCUST.XXJLN_FORMULA_HEADERS_REZA
where
FLAG is null;
commit;
delete
FROM
APPS.xxpran_subinv_locator_stg WHERE ITEM_NUMBER NOT IN (
SELECT
BB.SEGMENT1 FROM MTL_SYSTEM_ITEMS_B BB WHERE BB.ORGANIZATION_ID=102 AND
BB.SEGMENT1 IN (
SELECT
ITEM_NUMBER FROM APPS.xxpran_subinv_locator_stg
));
commit;
apps.xxpran_inv_assign('34220');
----------After
run one time then off this------------------
DECLARE
CURSOR c_header IS
SELECT DISTINCT formula_no
FROM
xxcust.XXJLN_FORMULA_HEADERS_REZA a
WHERE flag IS NULL
and
not exists (select 1 from
fm_form_mst b where a.FORMULA_NO = b.formula_no and
a.FORMULA_VERS=b.FORMULA_VERS);
CURSOR c_master (
p_formula varchar2
--TEST_M020
) IS
SELECT *
FROM
xxcust.XXJLN_FORMULA_HEADERS_REZA --mii_gmd_formula
WHERE flag IS NULL
AND formula_no = p_formula;
l_formula_header_tbl gmd_formula_pub.formula_insert_hdr_tbl_type;
l_formula
xxcust.XXJLN_FORMULA_HEADERS_REZA%ROWTYPE; -- mii_gmd_formula%ROWTYPE;
l_count NUMBER := 0;
l_loop_cnt NUMBER := 0;
l_record_count NUMBER := 0;
l_data VARCHAR2 (2000);
l_return_status VARCHAR2 (1);
l_status VARCHAR2 (1);
i NUMBER := 1;
l_dummy_cnt NUMBER;
l_api_version NUMBER := 1;
l_init_msg_list BOOLEAN;
l_commit BOOLEAN;
return_sts BOOLEAN;
v_item_id number;
v_organization_id number;
v_user_id number;
v_total_form number;
BEGIN
--FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
fnd_global.apps_initialize (user_id => 1110,
resp_id => 50774,
resp_appl_id => 555
);
-- FND_RESPONSIBILITY_VL , FND_user
/*FND_GLOBAL.APPS_INITIALIZE(user_id,
resp_id, resp_appl_id);*/
l_init_msg_list := TRUE;
l_commit := TRUE;
FOR
l_header IN c_header
LOOP
OPEN c_master(l_header.formula_no);
LOOP
FETCH c_master INTO l_formula;
exit WHEN c_master%NOTFOUND;
dbms_output.put_line (c_master%ROWCOUNT);
BEGIN
SELECT DISTINCT inventory_item_id
INTO v_item_id
FROM mtl_system_items_b
WHERE segment1 =
UPPER(l_formula.inventory_item_code);
SELECT organization_id
INTO v_organization_id
FROM mtl_parameters
WHERE organization_code =
l_formula.owner_organization_code;
SELECT user_id
INTO v_user_id
FROM fnd_user
WHERE user_name =
l_formula.owner_name;
EXCEPTION
WHEN others THEN
UPDATE xxcust.XXJLN_FORMULA_HEADERS_REZA
--mii_gmd_formula
set flag = 'E'
--, note = 'Ada exception'
WHERE formula_no =
l_formula.formula_no;
END;
l_formula_header_tbl
(c_master%ROWCOUNT).record_type := 'I';
l_formula_header_tbl
(c_master%ROWCOUNT).formula_no := UPPER(l_formula.formula_no);
l_formula_header_tbl
(c_master%ROWCOUNT).formula_vers := l_formula.formula_vers;
l_formula_header_tbl
(c_master%ROWCOUNT).formula_type := l_formula.formula_type;
l_formula_header_tbl
(c_master%ROWCOUNT).formula_desc1 := l_formula.formula_desc1;
l_formula_header_tbl
(c_master%ROWCOUNT).formula_class := l_formula.formula_class;
l_formula_header_tbl
(c_master%ROWCOUNT).inactive_ind := l_formula.inactive_ind;
l_formula_header_tbl
(c_master%ROWCOUNT).owner_organization_id := v_organization_id;
l_formula_header_tbl
(c_master%ROWCOUNT).formula_status := l_formula.formula_status;
l_formula_header_tbl
(c_master%ROWCOUNT).owner_id := v_user_id;
l_formula_header_tbl
(c_master%ROWCOUNT).line_type := l_formula.line_type;
l_formula_header_tbl
(c_master%ROWCOUNT).line_no := l_formula.line_no;
l_formula_header_tbl
(c_master%ROWCOUNT).inventory_item_id := v_item_id;
l_formula_header_tbl
(c_master%ROWCOUNT).qty := l_formula.qty;
l_formula_header_tbl
(c_master%ROWCOUNT).detail_uom := l_formula.detail_uom;
l_formula_header_tbl
(c_master%ROWCOUNT).release_type := l_formula.release_type;
l_formula_header_tbl (c_master%ROWCOUNT).scale_type_hdr
:= l_formula.scale_type_hdr;
l_formula_header_tbl
(c_master%ROWCOUNT).scale_type_dtl := l_formula.scale_type_dtl;
l_formula_header_tbl
(c_master%ROWCOUNT).cost_alloc := l_formula.cost_alloc;
l_formula_header_tbl
(c_master%ROWCOUNT).CONTRIBUTE_YIELD_IND := 'Y'; --
l_formula.CONTRIBUTE_YIELD_IND;
l_formula_header_tbl
(c_master%ROWCOUNT).PHANTOM_TYPE := l_formula.PHANTOM_TYPE;
l_formula_header_tbl
(c_master%ROWCOUNT).delete_mark := l_formula.delete_mark;
l_formula_header_tbl
(c_master%ROWCOUNT).CONTRIBUTE_STEP_QTY_IND := 'Y';
DBMS_OUTPUT.put_line ('Value Test ' ||
l_formula_header_tbl (1).formula_no);
-- UPDATE mii_gmd_formula
-- set flag = 'Y'
-- WHERE formula_no = l_formula.formula_no
-- AND line_no = l_formula.line_no
-- AND inventory_item_code =
l_formula.inventory_item_code;
END LOOP;
CLOSE c_master;
gmd_formula_pub.insert_formula
(p_api_version => 1.0,
p_formula_header_tbl =>
l_formula_header_tbl,
x_return_status =>
l_return_status,
x_msg_count => l_count,
x_msg_data =>
l_data
);
DBMS_OUTPUT.put_line ('l_return_status:'||l_return_status);
DBMS_OUTPUT.put_line ('l_data:'||l_data);
IF l_return_status = 'E' OR l_return_status
= 'U'
THEN
UPDATE
xxcust.XXJLN_FORMULA_HEADERS_REZA--mii_gmd_formula
set flag = l_return_status
--, note = l_data
WHERE formula_no = l_formula.formula_no;
ELSE
UPDATE
xxcust.XXJLN_FORMULA_HEADERS_REZA--mii_gmd_formula
set flag = 'Y'
-- , note = l_data
WHERE formula_no = l_formula.formula_no;
END
IF;
END LOOP;
/*
IF l_count >= 0
THEN
l_loop_cnt := 1;
LOOP
fnd_msg_pub.get (p_msg_index => l_loop_cnt,
p_data => l_data,
p_encoded => fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);
DBMS_OUTPUT.put_line ('Record = ' ||
l_loop_cnt);
DBMS_OUTPUT.put_line (l_data);
IF l_status = 'E' OR l_status = 'U'
THEN
l_data := CONCAT ('ERROR ',
l_data);
END IF;
DBMS_OUTPUT.put_line (l_data);
IF (l_status = 'U')
THEN
l_return_status := l_status;
ELSIF (l_status = 'E' AND
l_return_status <> 'U')
THEN
l_return_status := l_status;
ELSE
l_return_status := l_status;
END IF;
l_loop_cnt := l_loop_cnt + 1;
IF l_loop_cnt > l_count
THEN
EXIT;
END IF;
END LOOP;
END IF;*/
COMMIT;
update
XXCUST.XXJLN_FORMULA_HEADERS_REZA
set FLAG
=null
where
FLAG = 'E';
commit;
---select
count(1) into v_total_form from
XXCUST.XXJLN_FORMULA_HEADERS_REZA a where (a.flag is null or a.flag !='Y');
END;
END;
/
==================================================================================================
--
PCS_PACK
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'PCS','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.PCS_PACK'),1,'PI6.PCS_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b,fm_matl_dtl d
where
a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and
a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='PCS' );
-- KG
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'KG','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.KG_PACK'),1,'PI6.KG_PACK', null,
null,null form_org_id
from
fm_form_mst a , gmd_routings b,fm_matl_dtl d
where
a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and
a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='KG' );
-- RFT
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for ','Routing
For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'RFT','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.RFT_PACK'),1,'PI6.RFT_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b,fm_matl_dtl d
where
a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and
a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='RFT' );
-- SET
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'SET','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.SET_PACK'),1,'PI6.SET_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b,fm_matl_dtl d
where
a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and
a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='SET' );
-- SFT
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null, null,
TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'SFT','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.SFT_PACK'),1,'PI6.SFT_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b,fm_matl_dtl d
where
a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and
a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa where
a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='SFT' );
-- MTR
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'MTR','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.MTR_PACK'),1,'PI6.MTR_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b,fm_matl_dtl d
where
a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and
a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='MTR' );
-- PKT
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'PKT','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.PKT_PACK'),1,'PI6.PKT_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b,fm_matl_dtl d
where
a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and
a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='PKT' );
-- COI
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'COI','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.COI_PACK'),1,'PI6.COI_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b,fm_matl_dtl d
where
a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and
a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='COI' );
-- ROL
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'ROL','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.ROL_PACK'),1,'PI6.ROL_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b,fm_matl_dtl d
where a.formula_no
= b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and
a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='ROL' );
------YRD--------
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'YRD','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.YRD_PACK'),1,'PI6.YRD_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b, fm_matl_dtl d
where
---a.FORMULA_ID=d.FORMULA_ID
a.formula_no = b.routing_no(+) and
b.routing_no is null --and YIELD_UOM='PCS'
and a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='YRD' );
------DRM--------
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'DRM','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.DRM_PACK'),1,'PI6.DRM_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b, fm_matl_dtl d
where
---a.FORMULA_ID=d.FORMULA_ID
a.formula_no = b.routing_no(+) and
b.routing_no is null --and YIELD_UOM='PCS'
and a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='DRM' );
------LTR--------
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'LTR','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.LTR_PACK'),1,'PI6.LTR_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b, fm_matl_dtl d
where
---a.FORMULA_ID=d.FORMULA_ID
a.formula_no = b.routing_no(+) and
b.routing_no is null --and YIELD_UOM='PCS'
and a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='LTR' );
------GLN--------
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'GLN','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.GLN_PACK'),1,'PI6.GLN_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b, fm_matl_dtl d
where
---a.FORMULA_ID=d.FORMULA_ID
a.formula_no = b.routing_no(+) and
b.routing_no is null --and YIELD_UOM='PCS'
and a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='GLN' );
------MTR--------
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'MTR','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.MTR_PACK'),1,'PI6.MTR_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b, fm_matl_dtl d
where
---a.FORMULA_ID=d.FORMULA_ID
a.formula_no = b.routing_no(+) and
b.routing_no is null --and YIELD_UOM='PCS'
and a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='MTR' );
------CON--------
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES,
null,
null, TO_DATE(sysdate, 'DD/MON/YY'), null,
FORMULA_TYPE,
'CON','FMO', 370, a.SCALE_TYPE,'1', TO_DATE(sysdate, 'DD/MON/YY'), d.QTY
PROCESS_OUTPUT_QTY,
(select
OPRN_ID from gmd_operations where oprn_no='PI6.CON_PACK'),1,'PI6.CON_PACK',
null, null,null form_org_id
from
fm_form_mst a , gmd_routings b, fm_matl_dtl d
where
---a.FORMULA_ID=d.FORMULA_ID
a.formula_no = b.routing_no(+) and
b.routing_no is null --and YIELD_UOM='PCS'
and a.FORMULA_ID = d.FORMULA_ID
and d.LINE_TYPE = 1 and d.LINE_NO=1
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='CON' );
exec
APPS.MFZ_create_routing('NA');
select *
from gmd_routings where ROUTING_STATUS =
100
--update
gmd_routings set ROUTING_STATUS = 700
where ROUTING_STATUS = 100;
select *
from fm_form_mst where FORMULA_STATUS =
100;
--update
fm_form_mst set FORMULA_STATUS = 700 where FORMULA_STATUS = 100;
select *
from gmd_routings where ROUTING_STATUS = 100
---------
PENDING rOUTING --------------------------
select
----itm.attribute1 ou ,-- 0 nob_of_fromula, count(1) nob_of_routing, 0
nob_of_recipe
itm.PRIMARY_UOM_CODE, count(1) tm
from
fm_form_mst a , gmd_routings b, mtl_system_items_b itm
where
a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and
a.FORMULA_NO=itm.segment1 and itm.organization_id=102
group by PRIMARY_UOM_CODE
order by tm desc
---------------------------------------------------------------------------------------------------
CREATE OR
REPLACE PROCEDURE APPS.MFZ_create_routing (p_create_routing IN VARCHAR2)
IS
l_routings_type apps.gmd_routings%ROWTYPE;
l_routings_step_tab
apps.gmd_routings_pub.gmd_routings_step_tab;
l_routings_step_dep_tab
apps.gmd_routings_pub.gmd_routings_step_dep_tab;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_count NUMBER;
l_msg_ind VARCHAR2 (30);
l_routing_id NUMBER;
l_oprn_id NUMBER;
ln_dep_type NUMBER;
ln_suc_rec_cnt NUMBER := 0;
ln_rej_rec_cnt NUMBER := 0;
ln_lcu_count NUMBER;
ln_progress NUMBER := 0;
ln_count NUMBER;
ln_organization_id NUMBER;
lc_routing_uom VARCHAR2 (7);
l_msg varchar2(2000);
l_user_id NUMBER := 1110; --NUMBER := 1187;
l_TEST VARCHAR2 (10);
/*
DROP TABLE APPS.XXBLR_OPM_ROUTING_STG
CASCADE CONSTRAINTS;
CREATE
TABLE APPS.XXBLR_OPM_ROUTING_STG
(
SR_NO VARCHAR2(20 BYTE),
NEW_ROUTING_NO VARCHAR2(20 BYTE),
ROUTING_VERSION VARCHAR2(20 BYTE),
ROUTING_DESCRIPTION VARCHAR2(250 BYTE),
ROUTING_CLASS VARCHAR2(20 BYTE),
ROUTING_CLASS_DESC VARCHAR2(20 BYTE),
GD_VALID_DATE DATE DEFAULT
to_date(sysdate,'dd-mm-yy')
,
--, to_char(to_date(valid_from,'dd-mm-yy'),'dd-mon-yy') valid_from
--valid_from,
VALID_TO VARCHAR2(20 BYTE),
ROUTING_QTY FLOAT(126),
ROUTING_UOM VARCHAR2(20 BYTE),
OWNER_ORG_CODE VARCHAR2(20 BYTE),
ORGANIZATION_ID NUMBER,
TOTAL_LOSS
NUMBER,
RECORD_STATUS CHAR(1 BYTE) DEFAULT 'N',
VALID_FROM DATE,
PROCESS_OUTPUT_QTY NUMBER(10),
OPRN_ID NUMBER,
STEP_NUMBER NUMBER,
NEW_OPRN_NO VARCHAR2(250 BYTE),
ERROR_MESSAGE_ROUTING VARCHAR2(2000 BYTE),
ERROR_CODE_ROUTING VARCHAR2(250 BYTE),
RECORD_STATUS_ROUTING VARCHAR2(250 BYTE)
)
TABLESPACE
APPS_TS_TX_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE;
Insert into XXBLR_OPM_ROUTING_STG
select
distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for
','Routing For ') FORMULA_DES, null, null, TO_DATE('10/Nov/22', 'DD/MON/YY'),
null,
FORMULA_TYPE,
'PCS','FMO', 370, SCALE_TYPE,'1', TO_DATE('10/Nov/22', 'DD/MON/YY'), 1, (select
OPRN_ID from gmd_operations where oprn_no='PI6.PCS_PACK'),1,'PI6.PCS_PACK',
null, null,null form_org_id from fm_form_mst a , gmd_routings b
where
a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='PCS' );
Insert
into XXBLR_OPM_STEP_DEP_STG
select
distinct 'FMO', FORMULA_NO, '1','1','1','1','1', null, null,null form_org_id
from fm_form_mst a , gmd_routings b
where
a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and exists (select 1 from fm_matl_dtl aa
where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='PCS' );
commit;
*/
--===============================
--Cursor
to get new records count
--===============================
CURSOR lcu_count
IS
--
create table mfz_opm_routing_stg as select * FROM xxblr_opm_routing_stg
SELECT COUNT (*)
FROM xxblr_opm_routing_stg
WHERE record_status ='1' and
RECORD_STATUS_ROUTING is null
;-- gc_validation_flag;
--===============================
--Cursor
to get Routing Data
--===============================
CURSOR lcu_routing_data
IS
SELECT xors.sr_no
, xors.new_routing_no routing_no
, xors.routing_version
, xors.routing_description
, xors.routing_class
, xors.routing_class_desc
, gd_valid_date valid_from
--,
to_char(to_date(xors.valid_from,'dd-mm-yy'),'dd-mon-yy') valid_from
--xors.valid_from
, xors.valid_to
, xors.routing_qty
, xors.routing_uom
, xors.owner_org_code
, xors.organization_id
,
--substr(xors.total_loss,1,instr(xors.total_loss,'%')-1)
total_loss --xors.total_loss
FROM xxblr_opm_routing_stg xors
WHERE 1=1 and RECORD_STATUS_ROUTING is null
GROUP BY xors.sr_no
, xors.new_routing_no
, xors.routing_version
, xors.routing_description
, xors.routing_class
, xors.routing_class_desc
, xors.gd_valid_date
, xors.valid_to
, xors.routing_qty
, xors.routing_uom
, xors.owner_org_code
, xors.organization_id
, xors.total_loss
ORDER BY new_routing_no
;
--============================
--Cursor
to get Step data
--============================
CURSOR lcu_step_data (p_routing_num
VARCHAR2)--,p_step_num NUMBER)
IS
SELECT
new_routing_no,
new_oprn_no,
step_number,
oprn_id,
process_output_qty
FROM xxblr_opm_routing_stg
WHERE 1=1 and RECORD_STATUS_ROUTING is
null
--
AND step_number = p_step_num
AND new_routing_no = p_routing_num
GROUP BY new_routing_no,
new_oprn_no,
step_number,
oprn_id,
process_output_qty
ORDER BY step_number;
--=================================
--Cursor
to get Step Depency data
--=================================
CURSOR lcu_step_dep_data (p_routing_no
varchar2)
IS
SELECT
organization_code
, new_routing_no routing_no
, routing_version
, previous_step
, routing_step
, dependency_type
, transfer_percent
, error_code
, record_status
, error_message
FROM
xxblr_opm_step_dep_stg
WHERE
1=1
AND
new_routing_no = p_routing_no
GROUP BY
organization_code
, new_routing_no
, routing_version
, previous_step
, routing_step
, dependency_type
, transfer_percent
, error_code
, record_status
, error_message
ORDER BY previous_step;
TYPE tbl_routing_data IS TABLE OF
lcu_routing_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_per tbl_routing_data;
-- TYPE tbl_stepdep_data IS TABLE OF
lcu_step_dep_data%ROWTYPE INDEX BY BINARY_INTEGER;
-- ct_per tbl_stepdep_data;
BEGIN
fnd_global.apps_initialize (user_id => 1187,
resp_id => 22882,
resp_appl_id => 552
);
apps.fnd_file.put_line
(apps.fnd_file.LOG, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Executing CREATE_ROUTING
---------');
apps.fnd_file.put_line (apps.fnd_file.LOG,
RPAD (' ', 80, ' '));
apps.fnd_file.put_line
(apps.fnd_file.output, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.output,' --------- Executing CREATE_ROUTING
---------');
apps.fnd_file.put_line (apps.fnd_file.output,
RPAD (' ', 80, ' '));
OPEN lcu_count;
FETCH lcu_count INTO ln_lcu_count;
CLOSE lcu_count;
apps.fnd_file.put_line
(apps.fnd_file.LOG,'Number of Records Loaded:' || ln_lcu_count);
-------------------------------------------------------------------------
-- Bulk Insert Data into Qualification.
--------------------------------------------------------------------------
OPEN lcu_routing_data;
LOOP
lt_per.DELETE;
FETCH lcu_routing_data BULK COLLECT
INTO lt_per LIMIT 50000;
IF lt_per.COUNT > 0
THEN
apps.fnd_file.put_line
(apps.fnd_file.LOG,'COUNT-->' || lt_per.COUNT);
FOR i IN lt_per.FIRST ..
lt_per.LAST
LOOP
apps.fnd_file.put_line
(apps.fnd_file.LOG,'TRACK2');
l_routings_type := NULL;
l_routings_type.routing_id := NULL;
l_routings_type.routing_no := lt_per (i).routing_no;
l_routings_type.routing_vers := lt_per (i).routing_version;
l_routings_type.routing_desc
:= lt_per (i).routing_description;
l_routings_type.routing_class
:= NULL; --lt_per (i).routing_class;
l_routings_type.routing_qty := lt_per (i).routing_qty;
l_routings_type.item_um := NULL;
l_routings_type.delete_mark := 0;
l_routings_type.text_code := NULL;
l_routings_type.inactive_ind
:= 0;
l_routings_type.in_use := 0;
l_routings_type.creation_date
:= sysdate;
l_routings_type.created_by := 1110; --l_user_id;
l_routings_type.last_update_login
:= 76532411; --l_user_id;
l_routings_type.last_update_date
:= sysdate;
l_routings_type.last_updated_by
:= 1110; -- l_user_id;
l_routings_type.effective_start_date
:= sysdate; -- to_date(lt_per (i).valid_from);
l_routings_type.effective_end_date
:= NULL; --lt_per (i).valid_to;
l_routings_type.owner_id := 1110; -- l_user_id;
l_routings_type.project_id := NULL;
l_routings_type.routing_status
:= 3; --gn_status;
l_routings_type.process_loss
:= 0; --lt_per(i).total_loss;
l_routings_type.enforce_step_dependency := 1;
l_routings_type.owner_organization_id
:= 370; --lt_per (i).organization_id;
l_routings_type.routing_uom := lt_per (i).routing_uom;
l_routings_type.contiguous_ind := 0;
l_routings_type.fixed_process_loss
:= 0; --NULL;
l_routings_type.fixed_process_loss_uom
:= lt_per (i).routing_uom; --NULL;
l_count := 0;
l_routings_step_tab.DELETE;
apps.fnd_file.put_line
(apps.fnd_file.LOG,'TRACK3');
FOR step_rec IN
lcu_step_data(lt_per (i).routing_no)--,lt_per (i).step_number)
LOOP
apps.fnd_file.put_line
(apps.fnd_file.LOG,'lcu_step_data :'
||l_count);
apps.fnd_file.put_line
(apps.fnd_file.LOG,'STEP NUMBER:'
||step_rec.step_number);
l_count :=l_count + 1;
l_routings_step_tab
(l_count).routing_id := NULL;
--l_routing_id;
l_routings_step_tab
(l_count).routingstep_no := 1;
--step_rec.step_number;
l_routings_step_tab
(l_count).routingstep_id := NULL;
l_routings_step_tab
(l_count).oprn_id :=
step_rec.oprn_id;
l_routings_step_tab
(l_count).step_qty := 1000;
--step_rec.process_output_qty;
l_routings_step_tab
(l_count).steprelease_type := 1;
l_routings_step_tab
(l_count).text_code := NULL;
l_routings_step_tab
(l_count).last_updated_by := 1110; --l_user_id;
l_routings_step_tab
(l_count).created_by := 1110;
-- l_user_id;
l_routings_step_tab
(l_count).last_update_date :=
sysdate;
l_routings_step_tab
(l_count).creation_date :=
sysdate;
l_routings_step_tab
(l_count).last_update_login :=
76532411;-- l_user_id;
l_routings_step_tab
(l_count).minimum_transfer_qty := NULL;
--apps.fnd_file.put_line
(apps.fnd_file.LOG,'COUNT-->' || ct_per.COUNT);
END LOOP;
apps.fnd_file.put_line
(apps.fnd_file.LOG,'TRACK3');
ln_count :=0; l_TEST := '227';
l_routings_step_dep_tab.DELETE;
FOR ct_per IN
lcu_step_dep_data(lt_per(i).routing_no)
LOOP
ln_count := ln_count + 1;
apps.fnd_file.put_line
(apps.fnd_file.LOG,'lcu_step_dep_data: ' ||ln_count);
apps.fnd_file.put_line
(apps.fnd_file.LOG,'ROUTING STEP NO:'
||ct_per.routing_step);
apps.fnd_file.put_line
(apps.fnd_file.LOG,'PREVIOUS STEP NO:'
||ct_per.previous_step);
--Step Dependency
l_routings_step_dep_tab (ln_count).routingstep_no := 1; --ct_per.routing_step;
l_routings_step_dep_tab (ln_count).dep_routingstep_no :=0;-- ct_per.previous_step;
l_routings_step_dep_tab (ln_count).routing_id := NULL;--l_routing_id;
l_routings_step_dep_tab (ln_count).dep_type := ct_per.dependency_type;
l_routings_step_dep_tab (ln_count).rework_code := NULL;
l_routings_step_dep_tab (ln_count).standard_delay := 0;
l_routings_step_dep_tab (ln_count).minimum_delay := 0;
l_routings_step_dep_tab (ln_count).max_delay := NULL;
l_routings_step_dep_tab (ln_count).transfer_qty := 300;
l_routings_step_dep_tab (ln_count).item_um := NULL;
l_routings_step_dep_tab (ln_count).text_code := NULL;
l_routings_step_dep_tab (ln_count).last_updated_by := 1110;-- l_user_id;
l_routings_step_dep_tab (ln_count).created_by := 1110; -- l_user_id;
l_routings_step_dep_tab (ln_count).last_update_date := sysdate;
l_routings_step_dep_tab (ln_count).creation_date := sysdate;
l_routings_step_dep_tab (ln_count).last_update_login :=
76532411;-- l_user_id;
l_routings_step_dep_tab (ln_count).transfer_pct := ct_per.transfer_percent;
l_routings_step_dep_tab (ln_count).chargeable_ind := 0;
l_routings_step_dep_tab (ln_count).routingstep_no_uom := lt_per (i).routing_uom;
END LOOP;
BEGIN
apps.gmd_routings_pub.insert_routing
( p_api_version => 1.0
,
p_init_msg_list => TRUE
,
p_commit => FALSE
,
p_routings => l_routings_type
,
p_routings_step_tbl =>
l_routings_step_tab
,
p_routings_step_dep_tbl =>
l_routings_step_dep_tab
, x_message_count => l_msg_count
,
x_message_list =>
l_msg_data
,
x_return_status =>
l_return_status
);
l_TEST := '274';
apps.fnd_file.put_line
(apps.fnd_file.LOG,'RETURN_STATUS:-'|| l_return_status);
l_TEST := '278';
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'API RAISED ERROR: '||
SQLCODE|| '-'|| SQLERRM);
apps.fnd_file.put_line
(apps.fnd_file.LOG,'RETURN_STATUS:-'|| l_return_status);
END;
IF l_return_status <> 'S'
THEN
ln_rej_rec_cnt :=
ln_rej_rec_cnt + 1;
l_msg := '';
FOR i IN 1 .. l_msg_count
LOOP
l_msg := SUBSTR(l_msg || ' |M-
' || SUBSTR(apps.fnd_msg_pub.get(l_msg_count - i + 1, 'F'), 0, 200), 1, 2000);
END LOOP;
l_msg_data := l_msg;
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error
in OPRN INSERT API : ' || l_msg);
ELSE
ln_suc_rec_cnt :=
ln_suc_rec_cnt + 1;
END IF;
apps.fnd_file.put_line
(apps.fnd_file.LOG,'TRACK 14');
BEGIN
UPDATE xxblr_opm_routing_stg
SET record_status_routing = 'TEST: ' || l_TEST || ' : ' || l_return_status
,
error_code_routing = l_msg_count
, error_message_routing = l_msg_data
WHERE new_routing_no = lt_per(i).routing_no
AND routing_version = lt_per(i).routing_version;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table
updation:'||sqlcode||sqlerrm);
END;
BEGIN
UPDATE xxblr_opm_step_dep_stg
SET
record_status = l_return_status
, error_code = l_msg_count
, error_message = l_msg_data
WHERE new_routing_no = lt_per(i).routing_no
AND routing_version = lt_per(i).routing_version;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table
updation:'||sqlcode||sqlerrm);
END;
COMMIT;
END LOOP;
END IF;
apps.fnd_file.put_line
(apps.fnd_file.LOG,'
+----------------------------------------------------------+');
apps.fnd_file.put_line
(apps.fnd_file.LOG,' | Total records processed : '|| ln_suc_rec_cnt);
apps.fnd_file.put_line
(apps.fnd_file.LOG,' | Total records rejected
: '|| ln_rej_rec_cnt);
apps.fnd_file.put_line
(apps.fnd_file.LOG,' +----------------------------------------------------------+');
apps.fnd_file.put_line
(apps.fnd_file.LOG, ' ');
EXIT WHEN lcu_routing_data%NOTFOUND;
END LOOP;
CLOSE lcu_routing_data;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line
(apps.fnd_file.LOG,'MAIN-ERR-CREATE-ROUT :> ' || SQLERRM || ', ' ||
SQLCODE);
apps.fnd_file.put_line
(apps.fnd_file.LOG,' ---------
Procedure CREATE_ROUTING Exit ---------');
apps.fnd_file.put_line
(apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line
(apps.fnd_file.LOG, RPAD ('*', 80, '*'));
END mfz_create_routing;
/
---------------------------------------------------------------------------------------------------------
DROP
TABLE XXCUST.XXJLN_FORMULA_HEADERS_REZA CASCADE CONSTRAINTS;
CREATE
TABLE XXCUST.XXJLN_FORMULA_HEADERS_REZA
(
FORMULA_NO VARCHAR2(32 BYTE) NOT NULL,
FORMULA_VERS NUMBER DEFAULT 1 NOT NULL,
FORMULA_TYPE NUMBER(5) DEFAULT 1 NOT NULL,
FORMULA_DESC1 VARCHAR2(2000 BYTE),
FORMULA_CLASS VARCHAR2(8 BYTE),
INACTIVE_IND NUMBER(5) DEFAULT 0 NOT NULL,
FORMULA_UOM VARCHAR2(4 BYTE),
FORMULA_STATUS VARCHAR2(30 BYTE) DEFAULT 100 NOT NULL,
FLAG VARCHAR2(2 BYTE),
RECORD_TYPE VARCHAR2(12 BYTE),
OWNER_ORGANIZATION_CODE VARCHAR2(10 BYTE) DEFAULT 'FMO' NOT NULL,
OWNER_NAME VARCHAR2(20 BYTE) DEFAULT '34220' NOT NULL,
LINE_TYPE NUMBER(10),
LINE_NO INTEGER DEFAULT 1 NOT NULL,
QTY NUMBER(20,10),
RELEASE_TYPE VARCHAR2(10 BYTE),
COST_ALLOC VARCHAR2(10 BYTE),
SCALE_TYPE_HDR NUMBER(10) DEFAULT 1 NOT NULL,
DETAIL_UOM VARCHAR2(10 BYTE),
SCALE_TYPE_DTL INTEGER DEFAULT 1 NOT NULL,
PHANTOM_TYPE INTEGER DEFAULT 0 NOT NULL,
DELETE_MARK VARCHAR2(10 BYTE) DEFAULT 0 NOT NULL,
CONTRIBUTE_YIELD_IND VARCHAR2(10 BYTE) DEFAULT 'Y' NOT NULL,
INVENTORY_ITEM_CODE VARCHAR2(15 BYTE),
TYPE_OF_ITEM VARCHAR2(250 BYTE),
FILE_NAME VARCHAR2(250 BYTE),
ORG_CODE VARCHAR2(200 BYTE),
ORG_NAME VARCHAR2(300 BYTE),
NOTE VARCHAR2(250 BYTE),
FM_YES_NO VARCHAR2(10 BYTE)
)
----------------------------------------------------------------------------------
DROP
TABLE XXCUST.XXJLN_FORMULA_HEADERS_KYS CASCADE CONSTRAINTS;
CREATE
TABLE XXCUST.XXJLN_FORMULA_HEADERS_KYS
(
FORMULA_NO VARCHAR2(32 BYTE) NOT NULL,
FORMULA_VERS NUMBER DEFAULT 1 NOT NULL,
FORMULA_DESC1 VARCHAR2(450 BYTE),
OWNER_ORGANIZATION_CODE VARCHAR2(10 BYTE) DEFAULT 'FMO' NOT NULL,
TYPE_OF_ITEM VARCHAR2(250 BYTE),
INVENTORY_ITEM_CODE VARCHAR2(15 BYTE),
INVENTORY_ITEM_NAME VARCHAR2(400 BYTE),
FORMULA_UOM VARCHAR2(20 BYTE),
QTY NUMBER(20,10),
CREATE_USER VARCHAR2(50 CHAR) DEFAULT 'XL',
DATA_NOTE VARCHAR2(50 CHAR) DEFAULT 'N',
CREATE_DATE DATE DEFAULT sysdate,
SEQN NUMBER,
COST_ALLOC NUMBER(10,8) DEFAULT 0,
ISTATUS VARCHAR2(10 BYTE) DEFAULT 'N',
IDATE DATE DEFAULT sysdate
)
----------------------------------------------------------------------------------
No comments:
Post a Comment
Text Message