Case Study Items Not

Case Study –Items Not Costing with Inactive Bom Status





Doc ID

1. fact: Oracle Cost Management 11.5.6




3. fact: CSTRBICR6G - Cost Rollup - Print Report GUI




5. fact: CSTRBICR - Indented Bills of Material Cost Report




7. symptom: Assembly Cost Rollup - Print Report has inconsistent costs in the


8. report




10. symptom: Mismatch between sum of extended cost and the report summary




12. symptom: Item cost is being included for expense items




14. cause: Flags Not Set Correctly

fix:

For Issue 1:  According to documentation, Inactive items were not supposed to
be rolled up.  However, it was. The Include in Rollup flag and Inventory Asset
flag need to be turned off, and the zero cost updated to Frozen, and then
rolled up to the assembly level.

For Issue 2:  When submitting a request for cost rollup - print report, the
cost rollup is done and the "Bills of Material Indented Cost Report"
is printed.
This report shows up all the items in the BOM of the assembly for which rollup
is being done.

When Include in Rollup Flag is set to No for an item in the BOM of an
assembly, the item will not be removed from the BOM. It would merely not have
the item's cost included in the parent cost while rolling up costs.
The final assembly cost would not include the costs of items with Based on
Rollup Flag NO (the extended cost column would be zero for such items, even if
they are asset items having some unit cost), but they would show up in the
report with Include in Rollup Column as No.

When the Include in Rollup Flag is set to NO for an item in the BOM of a parent
item, (this is available in the Component Details Tab of Bill Of Materials form)
it would only default that value for BOMs which are created after that.

Cost rollup would refer only to the BOM of the assembly, and if Include in
Rollup is unchecked there, then the report would show the Include in Rollup
column as NO.  Unchecking Include in Rollup Flag for an item in the BOM
would print the Include in Rollup column in the report as zero, and prevent the
costs of an item from being rolled up.



(image placeholder)(image placeholder)(image placeholder)(image placeholder)(image placeholder)


(image placeholder)(image placeholder)(image placeholder)(image placeholder)(image placeholder)



(image placeholder)(image placeholder)(image placeholder)(image placeholder)(image placeholder)


(image placeholder)(image placeholder)(image placeholder)(image placeholder)(image placeholder)

(image placeholder)(image placeholder)(image placeholder)(image placeholder)(image placeholder)

(image placeholder)(image placeholder)(image placeholder)(image placeholder)(image placeholder)

(image placeholder)(image placeholder)(image placeholder)(image placeholder)(image placeholder)

(image placeholder)(image placeholder)(image placeholder)(image placeholder)(image placeholder)

(image placeholder)(image placeholder)(image placeholder)(image placeholder)(image placeholder)

(image placeholder)(image placeholder)
(image placeholder)

help


Bookmark
Go to End






Doc ID










































































































Doc ID

Chapter Seven

(image placeholder)
Chapter Seven
=================================
SET ECHO OFF
SET VERIFY OFF
DEFINE low_date = 01/01/1998
DEFINE high_date = 01/01/1999
SELECT  last_name ||', '|| job_id EMPLOYEES, hire_date
FROM    employees
WHERE   hire_date BETWEEN TO_DATE('&low_date', 'MM/DD/YYYY')
  AND   TO_DATE('&high_date', 'MM/DD/YYYY')
UNDEFINE low_date
UNDEFINE high_date
SET VERIFY ON
SET ECHO ON
(image placeholder)
=================================================
SET ECHO OFF
SET VERIFY OFF
COLUMN last_name HEADING "EMPLOYEE NAME"
COLUMN department_name HEADING "DEPARTMENT NAME"
SELECT  e.last_name, e.job_id, d.department_name
FROM    employees e, departments d, locations l
WHERE   e.department_id = d.department_id
AND     l.location_id = d.location_id
AND     l.city = INITCAP('&p_location')
/
COLUMN last_name CLEAR
COLUMN department_name CLEAR
SET VERIFY ON
SET ECHO ON

(image placeholder)
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
BREAK ON department_name
COLUMN department_name HEADING "DEPARTMENT|NAME"
COLUMN last_name HEADING "EMPLOYEE|NAME"
COLUMN hire_date HEADING "START|DATE"
COLUMN salary HEADING "SALARY" FORMAT $999,990.00
COLUMN asal HEADING "ANNUAL|SALARY" FORMAT $999,990.00

SELECT d.department_name, e.last_name, e.hire_date,
       e.salary, e.salary*12 asal
FROM   departments d, employees e, locations l
WHERE  e.department_id = d.department_id
AND    d.location_id   = l.location_id
AND    l.city          = '&p_location'
ORDER BY d.department_name
/
COLUMN department_name CLEAR
COLUMN last_name CLEAR
COLUMN hire_date CLEAR
COLUMN salary CLEAR
COLUMN asal CLEAR
CLEAR BREAK
SET VERIFY ON
SET FEEDBACK ON
SET ECHO ON
Chapter Six
2.
SELECT employee_id, last_name, salary
FROM   employees
WHERE  salary >
(SELECT AVG(salary)
                 FROM   employees)
ORDER BY salary

(image placeholder)

3.
SELECT employee_id, last_name
FROM   employees
WHERE  department_id IN (SELECT department_id
                         FROM   employees
                         WHERE  last_name like '%u%')

5.
SELECT last_name, salary
FROM   employees
WHERE  manager_id = (SELECT employee_id
                     FROM   employees
                     WHERE  last_name = 'King')

7. SELECT employee_id, last_name, salary
FROM   employees
WHERE  department_id IN (SELECT department_id
                         FROM   employees
                         WHERE  last_name like '%u%')
AND    salary > (SELECT AVG(salary)
                 FROM   employees)

(image placeholder)

Health Care Reimbursement

Health Care Reimbursement Claim Form

Amt 5:00

Paid to Mark Schofer

Reimbursement for 5.00 given to Max Schofer due to lack of
work previously done by the Tooth Fairy.  The tooth Fairy had always
shown up on previous occasions but I believe on this night (he/she)
dropped the ball of did not put the money in a place where Max could find it.

There fore I slid 5:00 under his pillow.

Could you guys forward a copy to said TOOTH FAIRY?


Best Regards
Mark 202-50-4378 (Max’s Dad)

OrderJoins

select
part.party_name cust,
soh.order_number Onum,
substr(soh.CUST_PO_NUMBER,1,6) PONum,
substr(sol.ordered_item,1,10) item,
substr(sot.attribute1,1,10) type,
sol.ship_from_org_id org,
rsu.location loc
from
oe_order_headers_all soh,
oe_order_lines_all sol,
oe_transaction_types_all sot,
org_organization_definitions ood,
hz_cust_site_uses_all rsu,
hz_cust_acct_sites_all addr,
hz_cust_accounts cust,
wsh_new_deliveries dhead,
wsh_delivery_assignments dass,
wsh_delivery_details wdd,
tk_carton_headers cart,
hz_parties part,
hz_locations loc,
hz_party_sites parts
where soh.header_id = sol.header_id
and sot.transaction_type_id = soh.order_type_id
AND ood.organization_id = sol.ship_from_org_id
AND sol.ship_to_org_id = rsu.SITE_USE_ID
AND wdd.source_header_id = soh.header_id --SOURCE_LINE_ID ALSO EXISTS
-- AND dhead.delivery_id = wdd.delivery_detail_id
and dhead.delivery_id =cart.delivery_id
and dhead.delivery_id = dass.delivery_id
and dass.delivery_detail_id = wdd.delivery_detail_id
and rsu.cust_acct_site_id = addr.cust_acct_site_id --ok
and addr.cust_account_id = cust.cust_account_id --ok
and cust.party_id = part.party_id --ok
and loc.location_id = parts.location_id --ok
and addr.party_site_id = parts.party_site_id --ok
and soh.order_number = 2000376
and rownum < 50


More


select hzp.party_name,
cust.party_id,
cust.account_number customer_number,
csu.site_use_code,
hzl.address1,
hzl.address2
from hz_cust_site_uses_all csu,
hz_cust_acct_sites_all addr,
hz_cust_accounts cust,
hz_parties hzp,
hz_locations hzl,
hz_party_sites hzps
where csu.cust_acct_site_id = addr.cust_acct_site_id
and addr.cust_account_id = cust.cust_account_id
and cust.party_id = hzp.party_id
and hzl.location_id = hzps.location_id
and addr.party_site_id = hzps.party_site_id
and csu.site_use_id = 114261

Building a list Of Values

Building a list Of Values

1. Create Record Group

Under Record Group tab on Navigator
(Copy from a Previous One)
The sql is located here.


2.Create LOV

Under LOV tab on Navigator
(Copy from a Previous One)
Important –Set Column   Mapping Properties Here

3.Attach LOV to Item

Under item attribute

4.Commit Rule Logic

SELECT ffv.flex_value MEANING ,ffv.flex_value LOOKUP_CODE ,ffv.description
  DESCRIPTION
from
fnd_flex_value_Sets ffvs, fnd_flex_values_vl ffv where
  ffvs.flex_value_set_id=ffv.flex_value_set_id and ffvs.flex_value_set_name=
  'TK_COMMIT_RULE' ORDER BY 1

5.Marketing Status

SELECT ffv.flex_value MEANING ,ffv.flex_value LOOKUP_CODE ,ffv.description
  DESCRIPTION
from
fnd_flex_value_Sets ffvs, fnd_flex_values_vl ffv where
  ffvs.flex_value_set_id=ffv.flex_value_set_id and ffvs.flex_value_set_name=
  'TK_INV_MKTG_STATUS' ORDER BY 1

6.Planner Code

select planner_code, description
from
mtl_planners where nvl(disable_date, sysdate+1) > sysdate and
  organization_id = :1 order by planner_code

If GAC errors out it

If GAC errors out it means one of two things...
 

1. Its an item that has uplift.

        (2 credits per one debit)
This is the rare case.
1,Query tk_gac_interface by transaction_id
2,remove uplift entry
3. Adjust dollars per Kriss Hunts instruction
 
Example Below
 
select transaction_id, base_transaction_value from mtl_transaction_accounts  where transaction_id = 15131018;update  mtl_transaction_accounts set base_transaction_value = -42.66 where transaction_id = 15131018and base_transaction_value = -41.09
 
delete from mtl_transaction_accountswhere transaction_id = 15131018and base_transaction_value = -6.16
 
select segment1 from mtl_system_items m, mtl_material_transactions twhere m.inventory_item_id = t.inventory_item_idand m.organization_id = t.organization_idand t.transaction_id = 15513879
 
 
 
No MTA transactions
 
1. Derive Item Number
 
select segment1 from mtl_system_items m, mtl_material_transactions twhere m.inventory_item_id = t.inventory_item_idand m.organization_id = t.organization_idand t.transaction_id = 15513879
 
2. UPDATE through hammer manager
 
update tk_gac_interface
set interfaced_flag = 'Y'
where transaction_id = 15513879
 
 
 

Form Notes

Form Notes

Table Name that is used is called.

TKOEXPALU_B

Underlying table changed to mtl_system_items

changed form to TKONTPALU