Mail Report SQL

select distinct t.transaction_type_ID,tp.TRANSACTION_TYPE_NAME, count(*) total_transaction
from mtl_material_transactions t, mtl_transaction_types tp
where trunc(transaction_date) between '01-JUN-2015' and '30-JUN-2015' and organization_id in
(select organization_id from mtl_parameters where process_enabled_flag='Y')
and t.TRANSACTION_TYPE_ID=tp.transaction_type_id
group by t.transaction_type_ID,TRANSACTION_TYPE_NAME

select * from mtl_material_transactions t, mtl_system_items_b i where trunc(transaction_date) between '01-MAY-2015' and
'31-MAY-2015' and t.organization_id =471
and t.inventory_item_id in (102863,103330)
and t.INVENTORY_ITEM_ID=i.INVENTORY_ITEM_ID and t.ORGANIZATION_ID=i.ORGANIZATION_ID

OPM RELATED TRANSACTIONAL ACCOUNTING

OPM RELATED TRANSACTIONAL ACCOUNTING
01. OPENING STOCK UPLOAD using Miscellaneous Receipt
a) Material Account A/C DR
Opening Balance Upload A/C CR

If InterOrg Transaction happened, then
a) Material Account(A/C) Dr
To InterOrg Payable A/C Cr

b) InterOrg Receivable A/C Dr
To Opening Balance Upload A/C Cr


if Miscellaneous Issue happened from Opening Balance upload, then
a) Opening Balance Upload A/C Dr
To Material Account(A/C) Cr

01. After Actual Cost Process, Need to Check:
SELECT * FROM cm_acer_msg where trunc(CREATION_DATE)='30-NOV-2015'
From Form : Navigation : Actual Costs-> Actual Cost Process--> Menu ->Actions->View Error messages
02.

Ledger PFL Primary Ledger Ledger Currency BDT Balance Type Actual Application Accounting Definition PRG Process Manufacturing Financials Version GL Date Apr 30, 2015 Journal Entry Description Line Accounting Class Account Entered Accounted Currency Debit Credit Debit Credit 1 Inventory Valuation 118-038-000-1113-00000-121001-000-000-00000-00000 BDT 2,040.39 2,040.39 2 Inventory Adjustments Expense 103-000-000-0000-00000-220034-000-000-00000-00000 BDT 2,040.39 2,040.39 3 Intracompany 103-000-000-0000-00000-126502-118-000-00000-00000 BDT 2,040.39 2,040.39 4 Intracompany 118-000-000-0000-00000-216200-103-000-00000-00000 BDT 2,040.39 2,040.39 Journal Entry Total 4,080.78 4,080.78

OPM FORMULA TO EXECUTION WORK FLOW TABLES

OPM Tables and Execution steps Workflow

01. FORMULA CLASS
a) GMD_FORMULA_CLASS_B
b) GMD_FORMULA_CLASS_TL

02. FORMULA
a) FM_FORM_MST_B // LINK : 01.A=02.A.CLASS_ID
b) FM_FORM_MST_TL
c) FM_MATL_DTL // LINK : 02.A.FORMULA_ID=02.C.FORMULA_ID

03. ACTIVITYS
a) GMD_ACTIVITIES_B
B) GMD_ACTIVITIES_TL

04.OPERATIONS
a) GMD_OPERATIONS_B
b) GMD_OPERATIONS_TL
c) GMD_OPERATION_ACTIVITIES //LINK: 03.A=04.C
d) GMD_OPERATION_RESOURCES // LINK: 05.A.04.D


05. RESOURCES
a) CR_RSRC_MST_B
b) CR_RSRC_MST_TL
c) GMP_RESOURCE_PARAMETERS
d) GMP_PROCESS_PARAMETER_B // LINK : 05.A=05.D
d) GMP_PROCESS_PARAMETER_TL

06.ROUTINGS
a) GMD_ROUTINGS_CLASS_B
b) GMD_ROUTINGS_CLASS_TL
c) GMD_ROUTINGS_B
d) GMD_ROUTINGS_TL
e) FM_ROUT_DTL // LINK : 04.A=06.E.OPERATION_ID
f) FM_ROUT_DEP // LINK : 06.E.06.F

07. RECIPES
a) GMD_RECIPES_B
b) GMD_RECIPES_TL
c) GMD_RECIPE_ROUTING_STEPS // 07.A=07.C AND 06.C=07.C
d) GMD_RECIPE_VALIDITY_RULES // ORG_ASSIGN





****************************************************************************************************
08. BATCH CREATION
a) GME_BATCH_HEADER
b) GME_BATCH_STEP_ACTIVITYS
c) GME_BATCH_STEP_RESOURCES
d) GME_MATERIAL_DETAILS
f) GME_RESOURCE_TXNS

09. BATCH RESERVATION
a) INV.MTL_RESERVATIONS // LINK : 08.D=09.A.DEMAND_SOURCE_LINE_ID

10. BATCH RELEASE
a) GMF.GMF_BATCH_REQUIRMENTS
b) INV.MTL_MATERIAL_TRANSACTIONS
c) INV.MTL_RESERVATIONS RECORD WILL BE DELETED OR NULL


11. BATCH STEP RELEASE


12. BATCH COMPLETED

SLA Details R12.1.3

SLA Details
# 01. Organization Not set in SLA Condition
//--------------------------------

select ORGANIZATION_CODE,ORGANIZATION_NAME from org_organization_definitions o,(select c.VALUE_CONSTANT
from xla_conditions c,xla_seg_rule_details rd--, org_organization_definitions o
where c.APPLICATION_ID=555 and c.APPLICATION_ID=rd.APPLICATION_ID and c.USER_SEQUENCE=2 and SOURCE_CODE='ORGANIZATION_CODE'
and SEGMENT_RULE_CODE='PRGINV' // for Misc Inventory Valuation and c.SEGMENT_RULE_DETAIL_ID=rd.SEGMENT_RULE_DETAIL_ID
--and o.ORGANIZATION_CODE=c.VALUE_CONSTANT
) a
where a.VALUE_CONSTANT(+)=o.ORGANIZATION_CODE and a.VALUE_CONSTANT is null


----------Another -------------------------------------
select * from
(select o.ORGANIZATION_CODE ORG_CODE, o.ORGANIZATION_NAME ORG_NAME, substr(o.ORGANIZATION_NAME,1,3) LOC, 'PF' org_type --,o.USER_DEFINITION_ENABLE_DATE, p.CREATION_DATE
from org_organization_definitions o, org_acct_periods p
where o.ORGANIZATION_ID=p.ORGANIZATION_ID(+) and o.ORGANIZATION_CODE not in ('FMO','IMO')
and p.PERIOD_NAME(+)='May-15-16' --and p.ORGANIZATION_ID is null
and o.ORGANIZATION_NAME like '%-PF-%'
--order by LOC desc
union all
select o.ORGANIZATION_CODE ORG_CODE, o.ORGANIZATION_NAME ORG_NAME, substr(o.ORGANIZATION_NAME,1,3) LOC, 'MAT' org_type --,o.USER_DEFINITION_ENABLE_DATE, p.CREATION_DATE
from org_organization_definitions o, org_acct_periods p
where o.ORGANIZATION_ID=p.ORGANIZATION_ID(+) and o.ORGANIZATION_CODE not in ('FMO','IMO')
and p.PERIOD_NAME(+)='May-15-16'-- and p.ORGANIZATION_ID is null
and o.ORGANIZATION_NAME like '%-MAT-%'
union all
select o.ORGANIZATION_CODE ORG_CODE, o.ORGANIZATION_NAME ORG_NAME, substr(o.ORGANIZATION_NAME,1,3) LOC, 'ME' org_type --,o.USER_DEFINITION_ENABLE_DATE, p.CREATION_DATE
from org_organization_definitions o, org_acct_periods p
where o.ORGANIZATION_ID=p.ORGANIZATION_ID(+) and o.ORGANIZATION_CODE not in ('FMO','IMO')
and p.PERIOD_NAME(+)='May-15-16' --and p.ORGANIZATION_ID is null
and o.ORGANIZATION_NAME like '%-ME-%'
union all
select o.ORGANIZATION_CODE ORG_CODE, o.ORGANIZATION_NAME ORG_NAME, substr(o.ORGANIZATION_NAME,1,3) LOC, 'LCM' org_type --,o.USER_DEFINITION_ENABLE_DATE, p.CREATION_DATE
from org_organization_definitions o, org_acct_periods p
where o.ORGANIZATION_ID=p.ORGANIZATION_ID(+) and o.ORGANIZATION_CODE not in ('FMO','IMO')
and p.PERIOD_NAME(+)='May-15-16' --and p.ORGANIZATION_ID is null
and o.ORGANIZATION_NAME like '%-LCM-%'
union all
select o.ORGANIZATION_CODE ORG_CODE, o.ORGANIZATION_NAME ORG_NAME, substr(o.ORGANIZATION_NAME,1,3) LOC, 'SELLING' org_type --,o.USER_DEFINITION_ENABLE_DATE, p.CREATION_DATE
from org_organization_definitions o, org_acct_periods p
where o.ORGANIZATION_ID=p.ORGANIZATION_ID(+) and o.ORGANIZATION_CODE not in ('FMO','IMO')
and p.PERIOD_NAME(+)='May-15-16' --and p.ORGANIZATION_ID is null
and o.ORGANIZATION_NAME like '35%'
union all
select o.ORGANIZATION_CODE ORG_CODE, o.ORGANIZATION_NAME ORG_NAME, substr(o.ORGANIZATION_NAME,1,3) LOC, 'OTHERS' org_type --,o.USER_DEFINITION_ENABLE_DATE, p.CREATION_DATE
from org_organization_definitions o, org_acct_periods p
where o.ORGANIZATION_ID=p.ORGANIZATION_ID(+) and o.ORGANIZATION_CODE not in ('FMO','IMO')
and p.PERIOD_NAME(+)='May-15-16' --and p.ORGANIZATION_ID is null
and o.ORGANIZATION_NAME not like '%-MAT-%' and  o.ORGANIZATION_NAME not like '%-PF-%' and  o.ORGANIZATION_NAME not like '%-ME-%' and  o.ORGANIZATION_NAME not like '%-LCM-%' and  o.ORGANIZATION_NAME not like '35%'
order by LOC desc, org_type desc, ORG_CODE asc
) aa,
(
select ORGANIZATION_CODE org_code,ORGANIZATION_NAME from org_organization_definitions o,(
select distinct c.VALUE_CONSTANT
from xla_conditions c,xla_seg_rule_details rd--, org_organization_definitions o
where c.APPLICATION_ID=555 and c.APPLICATION_ID=rd.APPLICATION_ID and c.USER_SEQUENCE=2 and SOURCE_CODE='ORGANIZATION_CODE'
and SEGMENT_RULE_CODE='PRGISP' -- for Misc Inventory Valuation
and c.SEGMENT_RULE_DETAIL_ID=rd.SEGMENT_RULE_DETAIL_ID
--and o.ORGANIZATION_CODE=c.VALUE_CONSTANT
) a
where a.VALUE_CONSTANT(+)=o.ORGANIZATION_CODE and a.VALUE_CONSTANT is null
) bb
where aa.org_code=bb.org_code
order by LOC desc, org_type desc, aa.ORG_CODE asc


# 02. ERROR CHECK in SLA
//--------------------------------

select distinct ORGANIZATION_CODE, o.ORGANIZATION_NAME
from xla_accounting_errors er, xla_ae_headers xah, xla_ae_lines xal,xla.xla_transaction_entities xte,
org_organization_definitions o-- Gmf_xla_Extract_Headers gxeh
where --trunc(er.CREATION_DATE)='02-DEC-2015' and
er.APPLICATION_ID=555 and er.APPLICATION_ID=xah.APPLICATION_ID
--and trunc(xah.CREATION_DATE) ='02-DEC-2015'
and er.ENTITY_ID=xah.ENTITY_ID and er.REQUEST_ID=xah.REQUEST_ID and
er.REQUEST_ID=:P_REQUEST_ID --2056025
and xah.APPLICATION_ID=xal.APPLICATION_ID and xah.APPLICATION_ID=555
and xah.AE_HEADER_ID=xal.AE_HEADER_ID
and trunc(xah.CREATION_DATE)=trunc(xal.CREATION_DATE)
-- and trunc(xah.CREATION_DATE)='02-DEC-2015'
and xah.REQUEST_ID=xal.REQUEST_ID and xah.REQUEST_ID=:P_REQUEST_ID --Accounting Request Id 2056025
and xah.PROGRAM_APPLICATION_ID=xal.PROGRAM_APPLICATION_ID
and er.ENTITY_ID=xte.ENTITY_ID and er.APPLICATION_ID=xte.APPLICATION_ID
and xte.SECURITY_ID_INT_1=o.ORGANIZATION_ID --and o.ORGANIZATION_code='A10'
--and xah.REQUEST_ID=gxeh.REQUEST_ID --and xah.EVENT_ID=gxeh.EVENT_ID
--GROUP BY o.ORGANIZATION_NAME


# 02.1 ERROR CHECK in SLA with Transaction ID
//----------------------------------------------------------
select  distinct count(ORGANIZATION_CODE) times,ORGANIZATION_id, ORGANIZATION_CODE, o.ORGANIZATION_NAME, er.ENCODED_MSG, xah.AE_HEADER_ID, xte.ENTITY_ID, SOURCE_ID_INT_1
from xla_accounting_errors er, xla_ae_headers xah, xla_ae_lines xal,xla.xla_transaction_entities xte,
org_organization_definitions o
where er.APPLICATION_ID=555 and
er.APPLICATION_ID=xah.APPLICATION_ID
and er.ENTITY_ID=xah.ENTITY_ID and er.REQUEST_ID=xah.REQUEST_ID and
er.REQUEST_ID=:P_REQUEST_ID --2056025
and xah.APPLICATION_ID=xal.APPLICATION_ID and xah.APPLICATION_ID=555
and xah.AE_HEADER_ID=xal.AE_HEADER_ID
and trunc(xah.CREATION_DATE)=trunc(xal.CREATION_DATE)
and xah.REQUEST_ID=xal.REQUEST_ID and xah.REQUEST_ID=:P_REQUEST_ID
and xah.PROGRAM_APPLICATION_ID=xal.PROGRAM_APPLICATION_ID and o.ORGANIZATION_CODE='478'
and er.ENTITY_ID=xte.ENTITY_ID and er.APPLICATION_ID=xte.APPLICATION_ID
and xte.SECURITY_ID_INT_1=o.ORGANIZATION_ID
GROUP BY ORGANIZATION_CODE, ORGANIZATION_id, o.ORGANIZATION_NAME , er.ENCODED_MSG, xah.AE_HEADER_ID, xte.ENTITY_ID, SOURCE_ID_INT_1


SOURCE_ID_INT_1=TRANSACTION_ID

select DISTRIBUTION_ACCOUNT_ID from mtl_material_transactions t where t.TRANSACTION_ID=120481385

select * from gl_code_combinations c where c.CODE_COMBINATION_ID=825229 

Check the Account Code is Active or Not. If inactive Active it then run Create Accounting program.





# 03. Org Location SQL for SLA
//--------------------------------


SELECT distinct hou.name, hou.SHORT_CODE, v.FLEX_VALUE, seg.application_column_name,
seg.segment_name, seg.SEGMENT_NUM, seg.flex_value_set_id, v.DESCRIPTION
FROM apps.hr_operating_units hou ,apps.gl_ledgers gled
,FND_ID_FLEX_SEGMENTS seg, FND_FLEX_VALUES_VL v
--,fnd_flex_values_vl ffv
WHERE gled.ledger_id = hou.set_of_books_id
AND seg.id_flex_code = 'GL#'
AND seg.id_flex_num = gled.CHART_OF_ACCOUNTS_ID
--and hou.organization_id=4
--and hou.NAME like '%Dairy%' and SEGMENT_NAME='Business Unit'
and seg.flex_value_set_id=v.FLEX_VALUE_SET_ID --and v.FLEX_VALUE_MEANING='101'


# 04. Org Location SQL for SLA
//--------------------------------




select DESCRIPTION, FLEX_VALUE_MEANING
,FLEX_VALUE,FLEX_VALUE_SET_ID,FLEX_VALUE_ID from FND_FLEX_VALUES_VL where DESCRIPTION LIKE '%-PF-%'

select * from xla_conditions a,  xla_seg_rule_details x
where a.APPLICATION_ID=x.APPLICATION_ID and x.APPLICATION_ID=555
and x.SEGMENT_RULE_CODE='INV' AND A.SOURCE_CODE='ORGANIZATION_CODE'
--AND X.SEGMENT_RULE_DETAIL_ID=50348


SEGMENT_RULE_DETAIL_ID


xla_seg_rules_fvl

SELECT USER_SEQUENCE,A.VALUE_CONSTANT, A.ORGANIZATION_CODE, A.ORGANIZATION_NAME,O1.ORGANIZATION_CODE, O1.ORGANIZATION_NAME,CONDITION_ID, ou.ORGANIZATION_ID ou_id, ou.NAME ou_name,
ou.SHORT_CODE ou_short_name
 FROM
(
SELECT seq.USER_SEQUENCE, CTN.VALUE_CONSTANT, o.ORGANIZATION_CODE, o.ORGANIZATION_NAME,CTN.CONDITION_ID
FROM xla_seg_rules_b srb,xla_seg_rules_tl srl,fnd_application_tl apl,xla_seg_rule_details srd, xla_conditions ctn,XLA_SEG_RULE_DETAILS seq
, ORG_ORGANIZATION_DEFINITIONS O
WHERE apl.APPLICATION_ID=555 and apl.APPLICATION_ID=srb.APPLICATION_ID
AND SRB.SEGMENT_RULE_CODE='PRG_CAD'
AND SRB.APPLICATION_ID=SRL.APPLICATION_ID AND SRB.SEGMENT_RULE_CODE=SRL.SEGMENT_RULE_CODE
AND  SRB.APPLICATION_ID=SRD.APPLICATION_ID AND SRB.SEGMENT_RULE_CODE=SRD.SEGMENT_RULE_CODE
AND SRD.APPLICATION_ID=CTN.APPLICATION_ID AND SRD.SEGMENT_RULE_DETAIL_ID=CTN.SEGMENT_RULE_DETAIL_ID AND CTN.SOURCE_CODE='ORGANIZATION_CODE'
AND CTN.VALUE_CONSTANT=O.ORGANIZATION_CODE and srb.APPLICATION_ID=seq.APPLICATION_ID and srb.SEGMENT_RULE_CODE=seq.SEGMENT_RULE_CODE and srd.APPLICATION_ID=seq.APPLICATION_ID
AND SRD.SEGMENT_RULE_DETAIL_ID=SEQ.SEGMENT_RULE_DETAIL_ID
) A,  ORG_ORGANIZATION_DEFINITIONS O1, hr_operating_units ou WHERE A.ORGANIZATION_CODE(+) =O1.ORGANIZATION_CODE and O1.operating_unit=ou.ORGANIZATION_ID
order by USER_SEQUENCE


SELECT * FROM GL_CODE_COMBINATIONS CC WHERE CC.CODE_COMBINATION_ID=50348


select * from  ORG_ORGANIZATION_DEFINITIONS O--  where APPLICATION_ID=555
        


          fnd_id_flex_structures_tl flx1
          fnd_id_flex_structures_tl flx2,
          xla_lookups lkp
==================
select distinct ORGANIZATION_CODE, o.ORGANIZATION_NAME, ENCODED_MSG
--COUNT(ORGANIZATION_CODE), o.ORGANIZATION_NAME
from xla_accounting_errors er, xla_ae_headers xah, xla_ae_lines xal,xla.xla_transaction_entities xte,
org_organization_definitions o-- Gmf_xla_Extract_Headers gxeh
where --trunc(er.CREATION_DATE)='03-JUL-2016' and
er.APPLICATION_ID=555 and er.APPLICATION_ID=xah.APPLICATION_ID
--and trunc(xah.CREATION_DATE) ='03-JUL-2016'
and er.ENTITY_ID=xah.ENTITY_ID and er.REQUEST_ID=xah.REQUEST_ID and
er.REQUEST_ID in (5475053,5460667,5463115) --2056025
and xah.APPLICATION_ID=xal.APPLICATION_ID and xah.APPLICATION_ID=555
and xah.AE_HEADER_ID=xal.AE_HEADER_ID and trunc(xah.CREATION_DATE)=trunc(xal.CREATION_DATE) --and trunc(xah.CREATION_DATE)='03-JUL-2016'
and xah.REQUEST_ID=xal.REQUEST_ID --and xah.REQUEST_ID=5475053 --Accounting Request Id 2056025
and xah.PROGRAM_APPLICATION_ID=xal.PROGRAM_APPLICATION_ID
and er.ENTITY_ID=xte.ENTITY_ID and er.APPLICATION_ID=xte.APPLICATION_ID
and xte.SECURITY_ID_INT_1=o.ORGANIZATION_ID --and o.ORGANIZATION_code='A10'
--and xah.REQUEST_ID=gxeh.REQUEST_ID --and xah.EVENT_ID=gxeh.EVENT_ID
--GROUP BY o.ORGANIZATION_NAME

============

select ORGANIZATION_CODE,ORGANIZATION_NAME from org_organization_definitions o,(select c.VALUE_CONSTANT
from xla_conditions c,xla_seg_rule_details rd--, org_organization_definitions o
where c.APPLICATION_ID=555 and c.APPLICATION_ID=rd.APPLICATION_ID and c.USER_SEQUENCE=2 and SOURCE_CODE='ORGANIZATION_CODE'
and SEGMENT_RULE_CODE='PRGINV' -- for Misc Inventory Valuation and c.SEGMENT_RULE_DETAIL_ID=rd.SEGMENT_RULE_DETAIL_ID
--and o.ORGANIZATION_CODE=c.VALUE_CONSTANT
) a
where a.VALUE_CONSTANT(+)=o.ORGANIZATION_CODE and a.VALUE_CONSTANT is null



======================================
select * from xla_conditions a,  xla_seg_rule_details x
where a.APPLICATION_ID=x.APPLICATION_ID and x.APPLICATION_ID=555
and x.SEGMENT_RULE_CODE='INV' AND A.SOURCE_CODE='ORGANIZATION_CODE'
--AND X.SEGMENT_RULE_DETAIL_ID=50348


SEGMENT_RULE_DETAIL_ID


xla_seg_rules_fvl


SLA-ACCT-ORG-CONDITIONS
======================================

SELECT A.ACCTCODE, USER_SEQUENCE,A.VALUE_CONSTANT, A.ORGANIZATION_CODE, A.ORGANIZATION_NAME,O1.ORGANIZATION_CODE, O1.ORGANIZATION_NAME,CONDITION_ID, ou.ORGANIZATION_ID ou_id, ou.NAME ou_name,
ou.SHORT_CODE ou_short_name
, GCC2.SEGMENT1 || '-'|| GCC2.SEGMENT2 || '-'|| GCC2.SEGMENT3 || '-'|| GCC2.SEGMENT4 || '-'|| GCC2.SEGMENT5 || '-'|| GCC2.SEGMENT6 || '-'|| GCC2.SEGMENT7 || '-'|| GCC2.SEGMENT8 || '-'|| GCC2.SEGMENT9 || '-'|| GCC2.SEGMENT10 LOCACCTCODE
 FROM
(
SELECT seq.USER_SEQUENCE, CTN.VALUE_CONSTANT, o.ORGANIZATION_CODE, o.ORGANIZATION_NAME,CTN.CONDITION_ID, GCC.SEGMENT1 || '-'|| GCC.SEGMENT2 || '-'|| GCC.SEGMENT3 || '-'|| GCC.SEGMENT4 || '-'|| GCC.SEGMENT5 || '-'|| GCC.SEGMENT6 || '-'|| GCC.SEGMENT7 || '-'|| GCC.SEGMENT8 || '-'|| GCC.SEGMENT9 || '-'|| GCC.SEGMENT10 ACCTCODE
FROM xla_seg_rules_b srb,xla_seg_rules_tl srl,fnd_application_tl apl,xla_seg_rule_details srd, xla_conditions ctn,XLA_SEG_RULE_DETAILS seq
, ORG_ORGANIZATION_DEFINITIONS O, gl_code_combinations gcc
WHERE apl.APPLICATION_ID=555 and apl.APPLICATION_ID=srb.APPLICATION_ID
AND SRB.SEGMENT_RULE_CODE='PRGINV'
AND SRB.APPLICATION_ID=SRL.APPLICATION_ID AND SRB.SEGMENT_RULE_CODE=SRL.SEGMENT_RULE_CODE
AND  SRB.APPLICATION_ID=SRD.APPLICATION_ID AND SRB.SEGMENT_RULE_CODE=SRD.SEGMENT_RULE_CODE
AND SRD.APPLICATION_ID=CTN.APPLICATION_ID AND SRD.SEGMENT_RULE_DETAIL_ID=CTN.SEGMENT_RULE_DETAIL_ID AND CTN.SOURCE_CODE='ORGANIZATION_CODE'
AND CTN.VALUE_CONSTANT=O.ORGANIZATION_CODE and srb.APPLICATION_ID=seq.APPLICATION_ID and srb.SEGMENT_RULE_CODE=seq.SEGMENT_RULE_CODE and srd.APPLICATION_ID=seq.APPLICATION_ID
AND SRD.SEGMENT_RULE_DETAIL_ID=SEQ.SEGMENT_RULE_DETAIL_ID  AND SRD.VALUE_CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
) A,
 ORG_ORGANIZATION_DEFINITIONS O1, hr_operating_units ou
 , gl_code_combinations gcc2, mtl_parameters p
 WHERE A.ORGANIZATION_CODE(+) =O1.ORGANIZATION_CODE and O1.operating_unit=ou.ORGANIZATION_ID
 AND O1.ORGANIZATION_ID=P.ORGANIZATION_ID AND P.MATERIAL_ACCOUNT=GCC2.CODE_COMBINATION_ID
AND  A.ORGANIZATION_CODE is null
order by USER_SEQUENCE


SELECT * FROM GL_CODE_COMBINATIONS CC WHERE CC.CODE_COMBINATION_ID=50348

SELECT * FROM HR_OPERATING_UNITS


select * from  ORG_ORGANIZATION_DEFINITIONS O--  where APPLICATION_ID=555
        


          fnd_id_flex_structures_tl flx1
          fnd_id_flex_structures_tl flx2,
          xla_lookups lkp
==================

select distinct ORGANIZATION_CODE, o.ORGANIZATION_NAME, ENCODED_MSG
--COUNT(ORGANIZATION_CODE), o.ORGANIZATION_NAME
from xla_accounting_errors er, xla_ae_headers xah, xla_ae_lines xal,xla.xla_transaction_entities xte,
org_organization_definitions o-- Gmf_xla_Extract_Headers gxeh
where --trunc(er.CREATION_DATE)='03-JUL-2016' and
er.APPLICATION_ID=555 and er.APPLICATION_ID=xah.APPLICATION_ID
--and trunc(xah.CREATION_DATE) ='03-JUL-2016'
and er.ENTITY_ID=xah.ENTITY_ID and er.REQUEST_ID=xah.REQUEST_ID and
er.REQUEST_ID in (5475053,5460667,5463115) --2056025
and xah.APPLICATION_ID=xal.APPLICATION_ID and xah.APPLICATION_ID=555
and xah.AE_HEADER_ID=xal.AE_HEADER_ID and trunc(xah.CREATION_DATE)=trunc(xal.CREATION_DATE) --and trunc(xah.CREATION_DATE)='03-JUL-2016'
and xah.REQUEST_ID=xal.REQUEST_ID --and xah.REQUEST_ID=5475053 --Accounting Request Id 2056025
and xah.PROGRAM_APPLICATION_ID=xal.PROGRAM_APPLICATION_ID
and er.ENTITY_ID=xte.ENTITY_ID and er.APPLICATION_ID=xte.APPLICATION_ID
and xte.SECURITY_ID_INT_1=o.ORGANIZATION_ID --and o.ORGANIZATION_code='A10'
--and xah.REQUEST_ID=gxeh.REQUEST_ID --and xah.EVENT_ID=gxeh.EVENT_ID
--GROUP BY o.ORGANIZATION_NAME

============

select ORGANIZATION_CODE,ORGANIZATION_NAME from org_organization_definitions o,(select c.VALUE_CONSTANT
from xla_conditions c,xla_seg_rule_details rd--, org_organization_definitions o
where c.APPLICATION_ID=555 and c.APPLICATION_ID=rd.APPLICATION_ID and c.USER_SEQUENCE=2 and SOURCE_CODE='ORGANIZATION_CODE'
and SEGMENT_RULE_CODE='PRGINV' -- for Misc Inventory Valuation and c.SEGMENT_RULE_DETAIL_ID=rd.SEGMENT_RULE_DETAIL_ID
--and o.ORGANIZATION_CODE=c.VALUE_CONSTANT
) a
where a.VALUE_CONSTANT(+)=o.ORGANIZATION_CODE and a.VALUE_CONSTANT is null


=====================
select *--vs.flex_value_set_name, st.id_flex_structure_code
from fnd_id_flex_structures st, fnd_flex_value_sets vs, fnd_id_flex_segments sg
where st.id_flex_num = sg.id_flex_num
and vs.flex_value_set_id = sg.flex_value_set_id
--and st.freeze_flex_definition_flag = 'Y'
--and st.application_id = 555
and st.id_flex_code = 'GL#'
order by 1,2



wsh_delivery_details

xxpfl_create_freight_cost

wsh_freight_costs


oe_order_lines_all ool
oe_order_headers_all ool

XX_FREIGHT_LODE_CHARGE

wsh_picking_batches

wsh_new_deliveries

wsh_delivery_legs

wsh_trip_stops


wsh_delivery_details


wsh_delivery_assignments


SELECT *--OBJECT_NAME
FROM USER_OBJECTS where upper(OBJECT_NAME) in ( upper('xxpfl_create_freight_cost'), upper('XX_FREIGHT_LODE_CHARGE'))

, wsh_delivery_Details wdd
, wsh_new_deliveries wnd
, wsh_delivery_assignments wda

select * from gl_item_cst

select * from xxtemp_org

SELECT L.FILE_DATA FILE_DATA,DBMS_LOB.GETLENGTH(L.FILE_DATA) FILE_LENGTH, L.LANGUAGE LANGUAGE, L.TERRITORY TERRITORY,
B.DEFAULT_LANGUAGE DEFAULT_LANGUAGE, B.DEFAULT_TERRITORY DEFAULT_TERRITORY,B.TEMPLATE_TYPE_CODE TEMPLATE_TYPE_CODE,
B.USE_ALIAS_TABLE USE_ALIAS_TABLE, B.START_DATE START_DATE, B.END_DATE END_DATE, B.TEMPLATE_STATUS TEMPLATE_STATUS,

B.USE_ALIAS_TABLE USE_ALIAS_TABLE, B.DS_APP_SHORT_NAME DS_APP_SHORT_NAME, B.DATA_SOURCE_CODE DATA_SOURCE_CODE, L.LOB_TYPE
LOB_TYPE FROM XDO_LOBS L, XDO_TEMPLATES_B B WHERE L.APPLICATION_SHORT_NAME= :1 AND L.LOB_CODE = :2 AND
L.APPLICATION_SHORT_NAME = B.APPLICATION_SHORT_NAME AND L.LOB_CODE = B.TEMPLATE_CODE AND (L.LOB_TYPE = 'TEMPLATE' OR
L.LOB_TYPE = 'MLS_TEMPLATE') AND ( (L.LANGUAGE = :3 AND L.TERRITORY = :4) OR (L.LANGUAGE= B.DEFAULT_LANGUAGE AND
L.TERRITORY= B.DEFAULT_TERRITORY ))

select * from GL_JE_LINES_V

select * from GL_JE_HEADERS_V JH,GL_JE_LINES_V JL
where JH.JE_CATEGORY='Miscellaneous Transaction' AND JH.JE_HEADER_ID=JL.JE_HEADER_ID AND
UPPER(JH.BATCH_PERIOD_NAME_QRY)=UPPER('APR-14-15')

SELECT BATCH_NAME, JE_LINE_NUM, SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5||'-'||SEGMENT6||'-'||SEGMENT7||'-'||SEGMENT8||'-'||SEGMENT9||'-'||SEGMENT10 ACCTS,
ENTERED_DR, ENTERED_CR, ACCOUNTED_DR, ACCOUNTED_CR,BATCH_RUNNING_TOTAL_DR, BATCH_RUNNING_TOTAL_CR, JL.DESCRIPTION,
BATCH_DATE_CREATED,CODE_COMBINATION_ID
from GL_JE_HEADERS_V JH,GL_JE_LINES_V JL
where JH.JE_CATEGORY='Miscellaneous Transaction' AND JH.JE_HEADER_ID=JL.JE_HEADER_ID AND
UPPER(JH.BATCH_PERIOD_NAME_QRY)=UPPER('APR-14-15')
AND ENTERED_DR<=0
ORDER BY BATCH_NAME, JE_LINE_NUM

116-059-000-1216-00000-121001-000-000-00000-00000
116-000-000-0000-00000-220034-000-000-00000-00000

SELECT * FROM MTL_MATERIAL_TRANSACTIONS WHERE TRUNC(TRANSACTION_DATE)='30-APR-2015' AND ORGANIZATION_ID IN (
select O.ORGANIZATION_ID--, DESCRIPTION, FLEX_VALUE_MEANING ,FLEX_VALUE,FLEX_VALUE_SET_ID,FLEX_VALUE_ID
from FND_FLEX_VALUES_VL FV, ORG_ORGANIZATION_DEFINITIONS O where TO_CHAR(FLEX_VALUE) IN
(
SELECT SEGMENT4
from GL_JE_HEADERS_V JH,GL_JE_LINES_V JL
where JH.JE_CATEGORY='Miscellaneous Transaction' AND JH.JE_HEADER_ID=JL.JE_HEADER_ID AND
UPPER(JH.BATCH_PERIOD_NAME_QRY)=UPPER('APR-14-15')
AND ENTERED_DR<=0 --AND SEGMENT4<>0000
)
AND FV.DESCRIPTION=O.ORGANIZATION_NAME
)
SELECT * FROM ORG_ORGANIZATION_DEFINITIONS O WHERE O.ORGANIZATION_CODE='L04'

O20 681

SELECT * FROM GL_ITEM_CST WHERE ORGANIZATION_ID=685 --AND PERIOD_ID


SELECT * FROM CM_ADJS_DTL WHERE ORGANIZATION_ID=685

'1216'

--DESCRIPTION LIKE '%-PF-%'

SLA -Subledger Accounting (SLA) Data Flow Chart
Inventory Subledger Accounting (SLA) Data Flow Chart
Subledger Accounting (SLA) Data Flow Chart



Important columns affected:

After a transaction is performed in forms:
mtl_material_transactions.costed_flag = 'N'

After the Cost Manager Picks up the data and processes it:
mtl_material_transactions.costed_flag is Null
xla_events.event_status_code = 'U'
xla_events.process_status_code = 'U'

After the Create Accounting - Cost Management is run:
xla_events.event_status_code = 'P'
xla_events.process_status_code = 'P'
xla_ae_headers.gl_transfer_status_code = 'N'
xla_ae_headers.gl_transfer_date is Null

After the Transfer To GL is run:
xla_ae_headers.gl_transfer_status_code = 'Y'
xla_ae_headers.gl_transfer_date is Not Null


Queries involved:
1.select * from mtl_material_transactions where transaction_id = '&transaction_id'
2.select * from mtl_transaction_accounts where transaction_id = '&transaction_id'
3.select * from XLA_TRANSACTION_ENTITIES_upg where source_id_int_1 = '&transaction_id'
4.select * from xla_events where entity_id in (select entity_id from XLA_TRANSACTION_ENTITIES_upg where source_id_int_1 = '&transaction_id')
5.select * from xla_distribution_links where source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and source_distribution_id_num_1 in (select inv_sub_ledger_id from mtl_transaction_accounts where transaction_id =

'&txnid')
6.select * from xla_ae_headers where ae_header_id in (select ae_header_id from xla_distribution_links where source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and source_distribution_id_num_1 in (select

inv_sub_ledger_id from mtl_transaction_accounts where transaction_id = '&txnid'))
7.select * from xla_ae_lines where ae_header_id in (select ae_header_id from xla_distribution_links where source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and source_distribution_id_num_1 in (select inv_sub_ledger_id

from mtl_transaction_accounts where transaction_id = '&txnid'))
8.select * from gl_import_references where gl_sl_link_table = 'XLAJEL' and gl_sl_link_id in ()
9.select * from gl_je_lines where je_header_id in () and je_line_num in ('')
10.select * from xla_accounting_errors where event_id in (select event_id from xla_events where entity_id in (select entity_id from XLA_TRANSACTION_ENTITIES_upg where source_id_int_1 = '&transaction_id'))








Oracle Apps Techno Functional................

Wednesday, 29 August 2012

Sub Ledger Accounting SLA (Complete Functional Information)
SubLegder Accounting in R12
SLA Part -1
In these article, we will explore the very basics of SLA and why it exists.
Firstly, what exactly does SLA do?
SLA is a module which now sits between the SubLedgers like AP/AR etc and the General Ledger. Have a look at this diagram below. As you will notice, SLA can act as a mediator between the subledgers and Oracle General Ledger.


Before we progress, some terminologies of R12 must be revisited. In 11i we had set of books, and in R12 we call them Ledgers. Likewise in R12 we also have secondary ledgers and reporting ledgers. Hence from 11i perspective think of Ledger as Set of Books. As for Subledger, a Subledger is nothing but a module like AP/AR/PO/Inventory etc.

In the diagram below, the second scenario is explained whereby let's say that payables module generates a charge account segment combination for an invoice distribution line as A.B.C.D. In such case, if SLA module is not customized, then the very same A.B.C.D combination will be passed to Oracle General Ledger via the SLA. And before you wonder... Yes, SLA module has its own set of tables to capture these accounting entries.
Please see the image below.


In the image above, SLA passes to GL whatever value is fed by the Subledger, in this case A.B.C.D


There are cases where SLA module can alter or massage the CCID or code combination that is generated in Subledger before transferring the same to Oracle General Leger. This scenario is explained whereby let's say that payables module generates a charge account segment combination for an invoice distribution line as A.B.C.D. In such case, if SLA module is customized, then the A.B.C.D code combination of Payables can be passed to Oracle General Ledger via the SLA as A.B1.C1.D instead.


The most important application however of the SLA is its ability to create shadow journals that contain different values or differing credit/debit entries for the transactions. This is the main reason why SLA module was invented/designed. Let us take an example. For company "Apps2Fusion UK", it might have operations in France, which is "Apps2Fusion France". With UK being the parent company, the French company has to do accounting journals in formats that can be reported as per French legislation and also as per UK legislation. For example, in France an inventory item is accounted as expense, whereas in UK the inventory item is accounted as an asset. In the example below, A.B.C.D is never passed to the GL. Instead to GL A.B1.C1.D is passed to the Primary Ledger FRANCE and A2.B2.C.D is passed to the Secondary Ledger UK. The picture below shows the role that SLA plays in such scenario.

Therefore in this article you have learnt the different usages of Subledger accounting
SLA PART 2 : Entities - Event Classes - Event Types

In this article you understand some of the basics fabrics/terminology used in SLA, i.e. Entities, Event Classes and Event Types. It is important for you to understand the variables within SLA engine which influence whether an accounting entry needs to be generated for a specific event within subledger like Payables or Receivables. For example in Procurement, there may be a need to generate accounting whenever a Purchase Order is encumbered. In case of SLA, the activity of "Encumbrance" against a Purchase Order is known as Event Type.
Likewise when a Payables Invoice is Validated, then you may want to create an accounting entry. In this case the "Invoice Validation" is an "Event Type". And your accounting rules for Invoice Validation will be attached against this specific "Event Type".

For Payables, an INVOICE transaction and a PAYMENT transactions are known as Entities within SLA.
Entities can be subdivided into various "Event Classes", for example Credit Memo, Debit Memo, Expense Reports, Invoices etc.
Further to this, against the Event classes we define Event Types, for example, whenever your Invoice is validated or cancelled or adjusted, you may want some specific accounts in the General Ledger to be impacted. Event types are therefore the types/list of events against transactions which you wish to account for in General Ledger.
This is explained in the diagram below.








Each entity is identified by unique identifier or primary key from the underlying tables.




SLA PART 3 : (Journal Line Definition) :

The Journal Line Definition "defines" how the entire journal is built. To create any journal, one of the key things is to get the CCID or the code combination of segments. SLA needs to know where this CCID will be coming from. You also need to know whether this CCID will be debit or this CCID will go into credit. Therefore you not just require the CCID, but you also need to decide whether a specific CCID will be debited or credited. In SLA, the "Journal Line Type" will specify whether the accounting entry is credit or debit. Also, you can then "attach something called an ADR to this Journal Line Type". The ADR returns the final code combination. Therefore Journal Line type will leverage the JLT+ADR to know which CCID is crediting and which CCID is debiting in the journal.


For each and every application there is a combination of event class and event type. Depending upon the combination of event class and event type the accounting gets triggered. The standard SLA out of the box from Oracle meets your requirement by 90%. For example you can fetch the standard accounting from payables or receivables options. However where these standard seeded accounting do not suffice, you can go and modify SLA to meet your business needs.

There is something called as Journal Entry Description. When a transaction is transferred as a journal, then every journal has credit/debit and description. The journal has description at header and also at line level. The JED allows you to generate the description of the Journal at both header and line level. For example you may want Customer Name or Customer Number in the journal description for a journal that is initiated from Oracle Receivables module. Using JED in SLA you can build header or line level descriptions.

The image below describes the end result journal that is produced by SLA





In JLT Journal Line Type, you can specify whether the entry is for credit or debit side. The Journal Line Type also provides options to do accounting for Gain/Loss of Foreign currency transactions. Further to that you can specify if SLA should merge the journal lines that have same CCID.






ADR - We specify how the account combination must be generated. We tell the system how we want the CCID should be built and transferred to the general ledger. You can either transfer the standard account as calculated within Subledger(AP or AR or PA etc) or the account generated from Subledger can be modified or replaced via ADR configuration within SLA.

Further to this, when defining ADR, you can specify the conditions under which a specific segment or CCID is returned. These conditions are like IF Conditions.


It is good to remember that the "Journal Line Definition=JED+JLT+ADR"

This is visible from the screenshot as shown below



You will notice that two "Journal Line Types" have been attached to this Journal Line Definition. The first journal line type assignment creates a credit line in the journal and the second journal line type assignment creates a debit line in the journal.


By now you would have understood the significance of Journal Line Definition. However you might be wondering how this Journal Line Definition gets associated with a Subledger transaction. For example, how does Oracle E-Business Suite decide which specific Journal Line Definition should be used when a specific event takes place against an invoice in Oracle Payables. In other words, how will SLA decide how the Journal will be constructed when an invoice is validated within Payables. We will learn this via AAD in next part of the article using Application Accounting Definitions.
SLA PART 4 : (Application Accounting Definitions)
In the SLA Part 2 article you Entities, Event Class and Event Types. In the SLA Part 3 you learnt the high level basics of Journal Line Definitions.
In this Part 4, you will see how the "Journal that gets constructed using Journal Line Definition" is associated with an underlying transaction in the respective module.




As seen in the image above, the Application Accounting Definitions [AAD] is attached to one or more Journal Line definition [JLD].

Effectively it means that AAD = JLD for an Event Class+ Event Type combination = ADR+JED+JLT for an Event Class+ Event Type combination

As a thumb rule, you must remember that each Application Accounting Definition [AAD] belongs to a module. Therefore if you have one Ledger implemented [ 11i Set Of Books] and two modules implemented like AP and AR, then you will have 2 AAD's defined, i.e. one for Payables and another for Receivables.


In the above picture only one Event Class of Invoice is used in AAD. However in reality you will have more than one event classes like Invoices, Payments etc associated with a AAD for Payables module.

Seeded Application Accounting Definitions are provided for each module out of the box by Oracle. However if the existing definitions do not meet your business requirements, then you can copy the existing AAD's to a custom AAD, and then make alterations to the custom AAD, which means creating custom JLT, custom JED and custom ADR as appropriate. It is important to remember that you must create a custom copy of an existing SLA component before making modifications.

In the next article you will see Subledger Accounting Method.
SLA PART 5 : (SubLedger Accounting Methods)

As we have seen in the Part 4 of SLA, the Application Accounting definition is used to decide two things
a. When a specific event within Subledger example Payables or Receivables becomes eligible for Accounting
b. How the journal is constructed.

However, each Primary Ledger[ 11i equivalent of primary set of book] and also each secondary ledger should be able to generate Journals as per their respective legislator requirements for all the modules implemented. This is where "Subledger Accounting Method" [SLAM] comes into the play. If you recollect from previous article, Application Accounting Definition is connected to only one module like Payables or Receivables etc. However a Ledger[11i SOB equivalent] needs accounting entries to be processed across many modules. Hence SLAM provides an umbrella to join accounting entries from various modules so that they can be channelled through to Oracle General Ledger. In other words a SLAM is a collection of accounting definitions for various modules in Oracle Apps. A SLAM is then attached to the Ledger[11i equivalent of Set Of Books].

Therefore the flow of accounting entries appears as shown below





The flow represented in simple equation appears as below
Ledger Defined in GL-->SLAM-->AAD--> [Event Class and Event Type]-->Liability Lines-->[JED+JLT+ADR]
Ledger Defined in GL-->SLAM-->AAD--> [Event Class and Event Type]-->Expense Lines-->[JED+JLT+ADR]


In order to assign the SLAM to a ledger, go to the General Ledger Super User responsibility and click on menu as shown below.






Click on update against the ledger



Attach the SLAM to this ledger





You can also modify the SLA accounting options using the two screenshots as shown below





SLA PART 6 : (Overall SLA Diagram)

The overall flow of the SLA can therefore be depicted as shown in image below.



Overall, when you create new definitions in SLA, you can follow the bottom up model.


The parallel flow on top, as shown in image below is to ensure both a Credit and Debit line gets created for a Journal.


SLA PART 7 : (Creating Journal Line Definition) :


In this article we will create a Journal Line Definition. You will basically apply the steps learnt thus far into practical implementation.

However to create a Journal Line Definition, we need to create the following
1. Journal Entry Description for journal line description2. Journal Line Type to mainly define credit or debit3. Account Derivation Rules for CCID used in journal line
Therefore typically, two set of JED,JLT and ADR’s are required, with one set each for Credit line, and the other set for the debit line.

In this article, we will create these three components.



Go to a subledger like Payables and within the SLA menu as shown below, you can open the Journal Enty Description screen. Click on New to create a new JED.





Create a new Journal Entry Description named ANIL_JED. We will use this for generating the Journal Line Description for both the credit and the debit lines.




The journal line description can be constructed by clicking on Details button. The description of the journal line can be a static text or dynamic text based on database sources within SLA or the combination of the two.


Here we are using the Supplier Name to construct the journal line description.



In addition to the dynamic journal line text, SLA allows you to put conditions.
For example
IF CONDITION1=TRUE, then Journal Line Description should be abcd
ELSE IF CONDITION2=TRUE, then Journal Line Description should be defg

Click on condition button to define the condition

After defining the Journal Entry description, now we can create Journal Line Type named ANIL_JLT_CREDIT for the credit line of the journal.






Specify the Transfer to GL in Summary or Detail mode. Also specify Merge, as explain in image below



Use the Payables setup option as source to build condition for JLT


In this example, we want to build a condition for “Journal Line Type” eligibility depending upon whether in the Payables Options Screen has Automatic Offset Method is set to None or Balancing or Account. In the above condition, we have placed an OR condition.




You can for example also build a condition based on Invoice Distribution type as shown below.


Next we need to define the Account Derivation Rule


Here we are creating an account derivation rule for the credit line.

An ADR can either return a full CCID or a specific segment. The values can be sourced either statically or from existing seeded dynamic sources in SLA. These seeded sources are mapped to database tables.

We are stating that this specific ADR named ANIL_LIABILITY_ADR will return a constant value in company segment


In the above image you will see that the value for Company segment can be derived from the corresponding value set.

As below, it is also possible for the specific ADR to return a full segment combination. In fact you can specify conditions within an ADR. When CONDITION1=TRUE then segment combonation a.b.c.d is returned or when CONDITION2=TRUE then d.e.f.g combination is returned for your account.


For the debit side, we are saying that the Account segment must always be 7450

The conditions can be defined. But conditions as evaluated as per the priority.



Finally we create a Journal Line Definition.



Here everything hangs together, JLTJLD,ADR=Journal Line Defnition

As shown below, we are saying that the CCID for the Credit Line of the journal will be calculated from ANIL_LIABILITY_ADR


And the CCID for the debit line will be calculated by the CCID value in Invoice Distribution line, with the specific segment from Account segment being replaced as per ANIL_EXPENSE_ADR.

For example, if the CCID in AP_INVOICE_DISTRIBUTION equates A.B.C.D.E.F then your journal line debit entry will be A.B.7450.D.E.F



SLA PART 8 : (Creating Application Accounting Definition AAD)
In the previous part of this SLA article, you have learnt creation of the Journal Line Definition. Now it is time to create AAD, which is "Application Accounting Definition".
The purpose of AAD in SLA is to dictate which "Journal Line Definition" must be used when a specific event takes place against a specific type of transaction in a specific module like Payables or Receivables. If you recollect, the "Journal Line Definition" definition creates a Credit Line and the Debit Line of a Journal.

Oracle ships out of the box an AAD for every simply module/application that uses SLA.
Hence for each application like AP,AR,PA,PO etc there will exist an existing AAD in the Subledger Modules. However, for this example we will create a new AAD for Payables.

In the previous article you created a Journal Line Definition that is responsible for constructing a Journal. However, in AAD screen you will specify when the Journal Line Definition will be used. In this case, as per the image below, we are stating that journal line definition ANIL_JLD should be used for creating journal whenever any event occurs against an Invoice in Payables.



You can also click on "Header Assignment" button in AAD to attached "Journal Entry Description" [JED] which dictates how the Journal Header description will be constructed. If you recollect, in Journal Entry Description, we concatenate static text and dynamic content from SLA Sources[mapped to DB columns or pl/sql functions] so as to construct a description for Journal Line or Journal header.


In this article we have seen that AAD is created for each module. However, in any implementation there is a need to perform accounting for all the modules. "APPS2FUSION UK" might be running Payables and Receivables and also Project Accounting. Hence we need to create a SLAM-Subledger Accounting Method.

Using AAD we specify the Journal creation rules per module. In SLAM we specify how the Journals must be built for the entire organization "APPS2FUSION UK" across Payables and Receivables and Project Accounting. The company "APPS2FUSION UK" will have a legal entity in UK, and hence the SLAM will be attached to the UK Legal Entity.


SLA PART 9 : Create SLAM(Sub Ledger Accounting Method)

In previous article we have seen that in SLA, the "Application Accounting Definition" is created for each module in EBusiness Suite. However, in any implementation there is a need to perform accounting across various different modules. For example, a company named "APPS2FUSION UK" might be running Payables and Receivables and also Project Accounting. Hence we need to create a SLAM [Subledger Accounting Method] that will take care of generating the Accounting journal lines for each of the module. Hence a SLAM is nothing but a grouping of all the AAD's possibly for a given chart of account.

Using AAD we specify the Journal creation rules per module. In SLAM we specify the applications/modules for which the Journals must be built for the entire organization such as "APPS2FUSION UK" across Payables and Receivables and Project Accounting. The decision of whether the journal must be created is delegated to the AAD. As for how the journal is constructed and how the accounts are derived is delegated to the Journal Line Definition.

The company such as "APPS2FUSION UK" will have a legal entity in UK, and hence the SLAM will be attached to the UK Legal Entity.

In the image below we are creating a SLAM named ANIL_SLAM, and attaching the AAD named ANIL_PAYABLES. This is a simplistic example, because in reality you will have the AAD's of other applications like Receivables , Project Accounting, Fixed Assets etc attached to the SLAM as well.



In the above image, click on Accounting Setups, and here you can attach the SLAM to a Ledger. To remind you, in R12, the Ledger is equivalent of set of books in 11i.



SLA PART 10 : (Testing and Explanation of SLA Concept)
In the SLA articles Part 1 to Part 6, we understood the basic concepts of Subledger Accounting.
In the SLA articles Part 7 to Part 9, we configured SLA for Payables as an example.

In this article, we will test the configuration to see the results of the configuration performed in Part 7 to Part 9 of the SLA articles. We will also explain the results of the test.

Our SLA setup was done for Payables, hence we will create an Invoice in Payables and check the accounting entries to reconcile those against our SLA setup.


If you recollect, we created an AAD named ANIL_PAYBLES which is attached to Oracle Payables module. This AAD will invoke JLD named ANIL_JLD to build the journal lines when an event takes place against the Payables invoice.

In the image below we are creating an Invoice from Payables responsibility and ensuring its invoice line distribution account is 01-110-6100-0000-000.
Next we click on Actions button and validate the invoice and create accounting entries.



Now check the accounting generated by clicking on menu title Reports||View Accounting as shown in the image below.





You will notice that the Debit entry of 1000 is charged to 01-110-7450-0000-000 instead of being charged to 01-110-6100-0000-000 in the Invoice Distribution Line of Payables. Please note that the third segments value of 6100 from Invoice Distribution has been replaced by 7450 from ADR named ANIL_EXPENSE_ADR as shown below.

The complete logic is described after this image.


Now, let us revisit how the credit line in the journal has been built. Typically Code Combination for the credit/liability accounting entry of the invoice line is picked from Payables Options setup of the operating unit. However in this case, in SLA we have hard coded the credit account to be 01.000.2220.0000.000. This was done by defining ANIL_LIABILIY_ADR as shown below. In this case the ADR returns the complete CCID because the Output Type radio button is set to Flexfield.

Hence, the SLA has generated the same Credit entry CCID for the invoice, as shown in image below
Needless to say that the ANIL_LIABILITY_ADR was attached to the Journal Line Type=ANIL_JLT_CREDIT.

Tables and Process

Tables and Process


01. Item Creation

MTL_SYSTEM_ITEMS_B


02. Shipping Network Creation
03. Internal Location creation and assign to Inventory Organization

04. Miscellaneous Receipts to make On-Hand

MTL_MATERIAL_TRANSACTIONS,
MTL_ONHAND_QUANTITIES_DETAIL

Create the Internal Customerassign the location, bill to ship to

05. Item Price

06. Create Internal Requisition

PO_REQUISITION_HEADERS_ALL(Segment1 is the IR No), PO_REQUISITION_LINES_ALL

Requisition Approval, Purchasing Super user- setup user> organizations> Financial Parameters> Human Resource Tab, Uncheck Use Appruval Hierarchicals for Position Approval
Define Jobs, setup -- Personnel -- Jobs

Setup approval group, setup -- Personnel --approvals-- Approval Groups

Approval Assignment

Employee creation/ Job Assignment

Run to Create Internal Request concurrent, Order will insert into the Order Management Interface Table with Location_id,

Import Internal Order

OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL ... FLOW_STATUS_CODE will show the status of each step
OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL ... FLOW_STATUS_CODE Will be Entered

After Book the order FLOW_STATUS_CODE Will be Booked of OE_ORDER_HEADERS_ALL and FLOW_STATUS_CODE Will be AWAITING_SHIPPING of OE_ORDER_LINES_ALL

WSH_DELIVERY_DETAILS will create with released_status=R(Ready to Release), WSH_DELIVERY_ASSIGNMENTS will create















Order entry OE_ORDER_HEADERS_ALL 1 record created in header table OE_ORDER_LINES_ALL Lines for particular records OE_PRICE_ADJUSTMENTS When discount gets applied OE_ORDER_PRICE_ATTRIBS If line has price attributes then populated OE_ORDER_HOLDS_ALL If any hold applied for order like credit check etc Order Booked OE_ORDER_HEADERS_ALL Booked_Flag=Y, Order booked. WSH_DELIVERY_DETAILS Status Opened WSH_DELIVERY_ASSIGNMENTS WSH_DELIVERY_ASSIGNMENTS.delivery_id will be NULL as still pick release operation is not performed as final delivery is not yet created. MTL_DEMAND. ‘Demand interface program’ is triggered in the background and demand of the item with specified quantity is created Order Scheduled/Reserved This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background(if scheduled) and quantities are reserved. OE_ORDER_LINES_ALL Awaiting Shipping MTL_RESERVATIONS This is only soft reservations. No physical movement of stock WSH_DELIVERY_DETAILS R: Ready to Release: Line is ready to be released Pick Released Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order. OE_ORDER_LINES_ALL WSH_DELIVERY_DETAILS S: Released to Warehouse WSH_NEW_DELIVERIES A new record is created in WSH_NEW_DELIVERIES with status_code = ‘OP’ (Open). WSH_NEW_DELIVERIES has the delivery records. WSH_DELIVERY_ASSIGNMENTS Deliveries get assigned WSH_PICKING_BATCHES After batch is created for pick release MTL_TXN_REQUEST_HEADERS A move order is created in Pick Release process which is used to pick and move the goods to staging area (here move order is just created but not transacted). MTL_TXN_REQUEST_HEADERS, MTL_TXN_REQUEST_LINES are move order tables MTL_TXN_REQUEST_LINES move order line Pick Confirm Pick Confirm is to transact the move order created in Pick Release process OE_ORDER_LINES_ALL flow_status_code =’PICKED’ MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS) MTL_MATERIAL_TRANSACTIONS MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Pick Transaciton MTL_TRANSACTION_ACCOUNTS updated with accounting information for mtl_material Transactions WSH_DELIVERY_DETAILS Y: Staged- Line has been picked and staged by Inventory MTL_ONHAND_QUANTITIES Ship Confirmed The goods are picked from staging area and given to shipping. “Interface Trip Stop” program runs in the backend. OE_ORDER_LINES_ALL .flow_status_code =‘SHIPPED’ Shipped_Quantity get populated WSH_DELIVERY_DETAILS Released_Status=C ;Shipped ;Delivery Note get printed Delivery assigned to trip stop quantity will be decreased MTL_TRANSACTIONS_INTERFACE Data from MTL_TRANSACTIONS_INTERFACE is moved to MTL_MATERIAL_TRANACTIONS MTL_MATERIAL_TRANSACTIONS updated with Sales Order Issue transaction WSH_NEW_DELIVERIES If Defer Interface is checked then OM & inventory not updated. If Defer Interface is not checked: Shipped OE_ORDER_LINES_ALL WSH_DELIVERY_LEGS 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip. OE_ORDER_HEADERS_ALL If all the lines get shipped then only flag N WSH_NEW_DELIVERIES Data Deleted MTL_RESERVATIONS Data Deleted MTL_DEMAND Data Deleted MTL_ONHAND_QUANTITIES Item deducted from MTL_ONHAND_QUANTITIES MTL_TRANSACTION_ACCOUNTS updated with accounting information. WSH_TRIPS WSH_TRIP_STOPS Auto Invoice After shipping the order the order lines gets eligible to get transfered to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. OE_ORDER_LINES_ALL invoice_interface_status_code = ‘YES’ WSH_DELIVERY_DETAILS Released_Status=I RA_INTERFACE_LINES_ALL Data will be populated after work flow process. RA_CUSTOMER_TRX_ALL After running Auto Invoice Master Program for RA_CUSTOMER_TRX_LINES_ALL Specific batch transaction tables get populated Close Order Last step of the process is to close the order which happens automatically once the goods are shipped OE_ORDER_LINES_ALL flow_status_code =’CLOSED’ and open_flag = ‘N’ Read more: http://www.oracleerpfunctional.com/2014/02/oracle-order-to-cash-tables-o2c-tables.html#ixzz3Yh5U984J

Cost Upload

Cost Upload For Cost Upload:
Crated by Mahfuz


CREATE TABLE APPS.PRAN_COST_ADJUSTMENT_STG
(
CALENDAR_CODE VARCHAR2(4 BYTE),
PERIOD_CODE VARCHAR2(4 BYTE),
COST_MTHD_CODE VARCHAR2(4 BYTE),
COST_CMPNTCLS_ID NUMBER(10),
COST_ANALYSIS_CODE VARCHAR2(32 BYTE),
COST_ADJUST_ID NUMBER(10),
ADJUST_QTY NUMBER,
ADJUST_COST NUMBER,
REASON_CODE VARCHAR2(4 BYTE),
ADJUST_STATUS NUMBER(5),
ADJUST_QTY_UOM VARCHAR2(3 BYTE),
COST_TYPE_ID NUMBER(15),
INVENTORY_ITEM_CODE VARCHAR2(12 BYTE),
ORGANIZATION_CODE VARCHAR2(12 BYTE),
PERIOD_ID NUMBER(15),-- GMF_PERIOD_STATUSES
FLAG VARCHAR2(2 BYTE),
ADJUSTMENT_DATE DATE DEFAULT SYSDATE,
ADJUSTMENT_IND NUMBER(1)
);



Insert into PRAN_COST_ADJUSTMENT_STG
(CALENDAR_CODE, PERIOD_CODE, COST_MTHD_CODE, COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
COST_ADJUST_ID, ADJUST_QTY, ADJUST_COST, REASON_CODE, ADJUST_STATUS,
ADJUST_QTY_UOM, COST_TYPE_ID, INVENTORY_ITEM_CODE, ORGANIZATION_CODE, PERIOD_ID,
FLAG, ADJUSTMENT_DATE, ADJUSTMENT_IND)
select null A1,null A2,null A3,1 A4,'DIR' A5,2003 A6,QTY A7,RATE A8,'SUP' A9, 0 A10,NULL A11,1000 A12,ITEM_CODE, ORG_CODE, 124 PERIOD,NULL FLG,TO_DATE('31/01/2015','DD-MM-YY'), 2 A13 from apps.xxtemp_org where file_name like 'INV%PFG%ALL%'



SET DEFINE OFF;
Insert into PRAN_COST_ADJUSTMENT_STG
(CALENDAR_CODE, PERIOD_CODE, COST_MTHD_CODE, COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
COST_ADJUST_ID, ADJUST_QTY, ADJUST_COST, REASON_CODE, ADJUST_STATUS,
ADJUST_QTY_UOM, COST_TYPE_ID, INVENTORY_ITEM_CODE, ORGANIZATION_CODE, PERIOD_ID,
FLAG, ADJUSTMENT_DATE, ADJUSTMENT_IND)
Values
(NULL, NULL, NULL, 1, 'DIR',
2003, 0, 90, 'SUP', 0,
NULL, 1000, '32706', 'G03', 184,
'Y', TO_DATE('05/19/2015 16:41:49', 'MM/DD/YYYY HH24:MI:SS'), 2);
COMMIT;


CREATE OR REPLACE PROCEDURE APPS.PRAN_COST_ADJUSTMENT_STG_proc as
cursor c1 is

select CALENDAR_CODE ,
PERIOD_CODE ,
COST_MTHD_CODE,
COST_CMPNTCLS_ID,
COST_ANALYSIS_CODE,
COST_ADJUST_ID ,
ADJUST_QTY ,
ADJUST_COST ,
REASON_CODE ,
ADJUST_STATUS ,
ADJUST_QTY_UOM ,
COST_TYPE_ID ,
I.INVENTORY_ITEM_ID,
S.inventory_item_CODE,
O.ORGANIZATION_ID ,
O.ORGANIZATION_CODE ,
PERIOD_ID,
ADJUSTMENT_DATE,
ADJUSTMENT_IND,
i.PRIMARY_UOM_CODE
from PRAN_COST_ADJUSTMENT_STG S, ORG_ORGANIZATION_DEFINITIONS O, MTL_SYSTEM_ITEMS_B I
WHERE S.ORGANIZATION_CODE=O.ORGANIZATION_CODE
AND S.INVENTORY_ITEM_CODE=I.SEGMENT1 AND O.ORGANIZATION_ID = I.ORGANIZATION_ID AND S.FLAG IS NULL

;

c1_rec c1%rowtype;
v_organization_id number(5);
v_inventory_item_id number(10);

Begin

for c1_rec in c1 loop
exit when c1%notfound;

begin

select
organization_id,
inventory_item_id
into v_organization_id, v_inventory_item_id
from mtl_system_items_b
where
inventory_item_id=c1_rec.inventory_item_id
and organization_id=c1_rec.organization_id;


end;



if
v_organization_id is not null
and v_inventory_item_id is not null
then
update PRAN_COST_ADJUSTMENT_STG
set flag='Y' where
inventory_item_CODE=c1_rec.inventory_item_CODE
and organization_CODE =c1_rec.organization_CODE;

insert into cm_adjs_dtl (
CALENDAR_CODE ,
PERIOD_CODE ,
COST_MTHD_CODE,
COST_CMPNTCLS_ID,
COST_ANALYSIS_CODE,
COST_ADJUST_ID ,
ADJUST_QTY ,
ADJUST_COST ,
REASON_CODE ,
ADJUST_STATUS ,
ADJUST_QTY_UOM ,
COST_TYPE_ID ,
INVENTORY_ITEM_ID,
ORGANIZATION_ID ,
PERIOD_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
delete_mark,
SUBLEDGER_IND,
ADJUSTMENT_DATE,
ADJUSTMENT_IND
)
values
(
C1_rec.CALENDAR_CODE ,
C1_rec.PERIOD_CODE ,
C1_rec.COST_MTHD_CODE,
C1_rec.COST_CMPNTCLS_ID,
C1_rec.COST_ANALYSIS_CODE,
--C1_rec.COST_ADJUST_ID ,
GEM5_COST_ADJUST_ID_S.nextval ,
C1_rec.ADJUST_QTY ,
C1_rec.ADJUST_COST ,
C1_rec.REASON_CODE ,
C1_rec.ADJUST_STATUS ,
--C1_rec.ADJUST_QTY_UOM ,
C1_rec.PRIMARY_UOM_CODE,
C1_rec.COST_TYPE_ID ,
C1_rec.INVENTORY_ITEM_ID,
C1_rec.ORGANIZATION_ID ,
C1_rec.PERIOD_ID,
sysdate,
1110,
sysdate,
1110,
0,
1,
TO_DATE( C1_rec.ADJUSTMENT_DATE,'DD-MM-YY'),
C1_rec.ADJUSTMENT_IND
);
else

update PRAN_COST_ADJUSTMENT_STG set flag='N' where
inventory_item_CODE=c1_rec.inventory_item_CODE
and organization_CODE =c1_rec.organization_CODE;


end if;
end loop;
commit;
End PRAN_COST_ADJUSTMENT_STG_proc;
/




Run actual Cost Process to see Item Cost

Stock Upload

Stock Upload insert into pran_item_stock_stg_M(SOURCE_CODE,SOURCE_LINE_ID,SOURCE_HEADER_ID,INVENTORY_ITEM_CODE,PROCESS_FLAG,VALIDATION_REQUIRED,TRANSACTION_MODE,TRANSACTION_QUANTITY,ORGANIZATION_CODE,SUBINVENTORY_CODE,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY)
select 'Adjusted Stock Upload' SOURCE_CODE, 1 SOURCE_LINE_ID, 1 SOURCE_HEADER_ID, ITEM_CODE INVENTORY_ITEM_CODE, 1 PROCESS_FLAG, 2 VALIDATION_REQUIRED, 3 TRANSACTION_MODE, qty TRANSACTION_QUANTITY, ORG_CODE ORGANIZATION_CODE, SUBINV_CODE SUBINVENTORY_CODE, TO_DATE('29/05/2015','dd-mm-yy') LAST_UPDATE_DATE, '1110' last_updated_by, TO_DATE('29/05/2015','dd-mm-yy') CREATION_DATE, '1110' CREATED_BY
from apps.xxtemp_org where upper(file_name) like upper(&flnm)



For Stock Upload:
Crated by Mahfuz


CREATE TABLE APPS.PRAN_ITEM_STOCK_STG_M
(
TRANSACTION_INTERFACE_ID NUMBER,
SOURCE_CODE VARCHAR2(30 BYTE),
SOURCE_LINE_ID NUMBER,
SOURCE_HEADER_ID NUMBER,
INVENTORY_ITEM_CODE VARCHAR2(10 BYTE),
PROCESS_FLAG NUMBER(1),
VALIDATION_REQUIRED NUMBER,
TRANSACTION_MODE NUMBER,
TRANSACTION_QUANTITY NUMBER,
ORGANIZATION_CODE VARCHAR2(10 BYTE),
PRIMARY_QUANTITY NUMBER,
TRANSACTION_UOM VARCHAR2(3 BYTE),
TRANSACTION_DATE DATE,
SUBINVENTORY_CODE VARCHAR2(10 BYTE),
LOCATOR_ID NUMBER,
TRANSACTION_TYPE_ID NUMBER,
TRANSACTION_COST NUMBER,
DISTRIBUTION_ACCOUNT_ID NUMBER,
LAST_UPDATE_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER NOT NULL,
CREATION_DATE DATE NOT NULL,
CREATED_BY NUMBER NOT NULL,
FLAG VARCHAR2(2 BYTE)
);


insert into pran_item_stock_stg_M(SOURCE_CODE,SOURCE_LINE_ID,SOURCE_HEADER_ID,INVENTORY_ITEM_CODE,PROCESS_FLAG,VALIDATION_REQUIRED,TRANSACTION_MODE,TRANSACTION_QUANTITY,ORGANIZATION_CODE,SUBINVENTORY_CODE,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY) values ('Adjusted Stock Upload',1,1,'32706',1,2,3,'1738', 'G03','G03.FG',to_date('31/01/2015','dd-mm-yy'),1110,to_date('31/01/2015','dd-mm-yy'),1110);


CREATE OR REPLACE PROCEDURE APPS.pran_item_stock_stg_proc as
cursor c1 is

select transaction_interface_id,source_code,source_line_id,
source_header_id,i.inventory_item_id, inventory_item_code , primary_uom_code ,process_flag,validation_required,
transaction_mode,transaction_quantity,o.organization_id, o.organization_code, primary_quantity,
primary_uom_code transaction_uom ,transaction_date ,subinventory_code,locator_id,
transaction_type_id,transaction_cost,distribution_account_id,
si.last_update_date,si.last_updated_by,si.creation_date,si.created_by from pran_item_stock_stg_M si, mtl_system_items_b i, org_organization_definitions o
where si.INVENTORY_ITEM_CODE=i.SEGMENT1 and si.ORGANIZATION_CODE=o.ORGANIZATION_CODE and o.ORGANIZATION_ID=i.ORGANIZATION_ID and FLAG is null

;

c1_rec c1%rowtype;
v_organization_id number(5);
v_inventory_item_id number(10);
v_primary_uom_code varchar2(6);
v_inventory_location_id number(10);
v_subinventory_code varchar2(10);
v_code_combination_id number(10);
v_transaction_type_id number(8);
Begin




for c1_rec in c1 loop
exit when c1%notfound;

begin

-- location
select
inventory_location_id,
subinventory_code
into v_inventory_location_id, v_subinventory_code
from mtl_item_locations
where subinventory_code =c1_rec.subinventory_code;



exception
when no_data_found then

v_organization_id :=null;
v_inventory_item_id :=null;
v_primary_uom_code :=null;
v_inventory_location_id :=null;
v_subinventory_code :=null;


end;



if

v_inventory_location_id is not null
and v_subinventory_code is not null then

update
pran_item_stock_stg_m set flag='Y' where
INVENTORY_ITEM_CODE=c1_rec.INVENTORY_ITEM_CODE
and organization_code =c1_rec.organization_code
and SUBINVENTORY_CODE =c1_rec.SUBINVENTORY_CODE
;

insert into mtl_transactions_interface
(
transaction_interface_id,
source_code,
source_header_id,
source_line_id,
inventory_item_id,
process_flag,
validation_required,
transaction_mode,
transaction_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
organization_id,
transaction_date,
transaction_type_id,
distribution_account_id,
transaction_uom,
subinventory_code,
locator_id,
TRANSACTION_COST
)
values
(
mtl_material_transactions_s.NEXTVAL,
--c1_rec.source_code,
'Adjusted Stock Upload',
1,
1,
c1_rec.inventory_item_id,
1,
2,
3,
c1_rec.transaction_quantity,
sysdate,
1110,
sysdate,
1110,
c1_rec.organization_id,
--c1_rec.transaction_date,
to_date('31/01/2015','dd-mm-yy'),
--
42,
c1_rec.distribution_account_id,
--128305,
c1_rec.transaction_uom,
c1_rec.subinventory_code,
--c1_rec.locator_id,
v_inventory_location_id,
null
);
else

update pran_item_stock_stg_m set flag='N' where
inventory_item_code=c1_rec.inventory_item_code
and organization_code =c1_rec.organization_code
and transaction_uom =c1_rec.transaction_uom
and locator_id =c1_rec.locator_id
and subinventory_code =c1_rec.subinventory_code
and distribution_account_id =c1_rec.distribution_account_id
and transaction_type_id =c1_rec.transaction_type_id;

end if;

end loop;

commit;
End pran_item_stock_stg_proc;
/
Run Lunch process transaction manager.
Navigate: PFL Inventory Super User > Setup > Transactions > Interface Manager

select Material Transactions and then Tools > Launch Manager

OPM- Production, How to know Batch Status

Batch Status




SELECT o.ORGANIZATION_CODE, o.ORGANIZATION_NAME, o.ORGANIZATION_ID,
gbh.BATCH_NO,decode(gbh.BATCH_STATUS,3,'Completed',4,'Closed',2,'WIP',
'Just Created') Batch_Status
FROM GME_BATCH_HEADER gbh,
ORG_ORGANIZATION_DEFINITIONS o
WHERE CREATION_DATE >=TO_DATE('01/05/2015','dd-mm-yy')
AND gbh.ORGANIZATION_ID=o.ORGANIZATION_ID
ORDER BY BATCH_STATUS, o.ORGANIZATION_CODE

Uploaded Formula History

Uploaded Formula History
click here

Item Assign to an Organization

01. Create a temp Table:
xxpran_item_assig_stg(item_number,ORG_CODE,MSG,SUBINV_CODE)

02.Insert required data to this temp table.
declare
cursor c1 is select * from xxpran_item_assig_stg;
/******* Variable Declaration ************/

g_user_id fnd_user.user_id%TYPE :=NULL;
l_appl_id fnd_application.application_id%TYPE;
l_resp_id fnd_responsibility_tl.responsibility_id%TYPE;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := fnd_api.g_false;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
x_message_list error_handler.error_tbl_type;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
l_org_id number(3);
l_item_id number(20);
l_flag varchar2(4) default'A';
l_msg varchar2(200);
BEGIN
SELECT fa.application_id INTO l_appl_id
FROM fnd_application fa
WHERE fa.application_short_name = 'INV';

SELECT fr.responsibility_id INTO l_resp_id
FROM fnd_application fa, fnd_responsibility_tl fr
WHERE fa.application_short_name = 'INV'
AND fa.application_id = fr.application_id
AND UPPER (fr.responsibility_name) = 'INVENTORY';

fnd_global.apps_initialize (g_user_id, l_resp_id, l_appl_id);

FOR x1 IN c1 LOOP
/* Validation For Organization */
begin
select organization_id into l_org_id
from ORG_ORGANIZATION_DEFINITIONS
where organization_code = x1.org_code;
exception
when others then
l_flag :='E';
l_msg :='Organization Code'||x1.org_code||' not in system';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
end;

/* Validation For Inventory Item Id */
begin
select inventory_item_id into l_item_id
from mtl_system_items_b
where segment1 = x1.item_number
and organization_id = 102 --- IMO org id
;
exception
when others then
l_flag :='E';
l_msg :='Inventory Item Sengent1 '||x1.item_number||' not in system';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
end;

if l_flag!='E' then
EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(
P_API_VERSION => l_api_version
, P_INIT_MSG_LIST => l_init_msg_list
, P_COMMIT => l_commit
, P_INVENTORY_ITEM_ID => l_item_id
-- , p_item_number => 000000000001035
, p_organization_id => l_org_id
-- , P_ORGANIZATION_CODE => 'DXN'
-- , P_PRIMARY_UOM_CODE => 'EA'
, X_RETURN_STATUS => x_return_status
, X_MSG_COUNT => x_msg_count
);
fnd_file.PUT_LINE(fnd_file.log,'Status: '||x_return_status);

-- update item status flag and those items need to assign to subinventory
update xxpran_item_assig_stg set msg='AGN_OK' where item_number=x1.item_number;
-- delete from xxpran_item_assig_stg where item_number=x1.item_number;
--
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
fnd_file.PUT_LINE(fnd_file.log,'Error Messages :');
Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
FOR j IN 1..x_message_list.COUNT LOOP
fnd_file.PUT_LINE(fnd_file.log,x_message_list(j).message_text);
END LOOP;
END IF;
end if;

END LOOP;
EXCEPTION
WHEN OTHERS THEN
fnd_file.PUT_LINE(fnd_file.log,'Exception Occured :');
fnd_file.PUT_LINE(fnd_file.log,SQLCODE ||':'||SQLERRM);
END;
/



EXEC APPS.XXPFL_SUBINV_M('M');


CREATE OR REPLACE PROCEDURE APPS.XXPFL_SUBINV_M(MM IN VARCHAR2) IS

    M_EXISTS VARCHAR2 (1);
    L_ERR_FLAG CHAR (1) := 'N';

    CURSOR C1 IS
   
    SELECT DISTINCT i.INVENTORY_ITEM_ID ITEMNO, ig.ITEM_NUMBER ITEM, o.ORGANIZATION_ID,ORGANIZATION_CODE, SUBINV_CODE
    FROM xxpran_item_assig_stg IG, ORG_ORGANIZATION_DEFINITIONS O, mtl_system_items_b i
    WHERE MSG='AGN_OK'   
    AND IG.ORG_CODE=O.ORGANIZATION_CODE
    and ig.ITEM_NUMBER=i.SEGMENT1
    and i.ORGANIZATION_ID=102;
    
  --  CURSOR C1 IS    SELECT  ORGANIZATION_ID,ORGANIZATION_CODE    FROM    org_organization_definitions  WHERE   ORGANIZATION_ID not in(102);

    -----  CURSOR SUB_INV(P_ORG NUMBER,P1_ITEM_ID NUMBER )  IS
    CURSOR SUB_INV(P_ORG NUMBER,P_SEC_INV VARCHAR2,P1_ITEM_ID NUMBER) IS
   
   
                        SELECT  MSI.INVENTORY_ITEM_ID,MP.ORGANIZATION_ID,
                                MS.SECONDARY_INVENTORY_NAME     SECONDARY_INVENTORY,
                                SYSDATE                         LAST_UPDATE_DATE,
                                1110                            LAST_UPDATED_BY,
                                SYSDATE                         CREATION_DATE,
                                1110                            CREATED_BY,
                                75516                           LAST_UPDATE_LOGIN,
                                6                               INVENTORY_PLANNING_CODE
                        FROM    MTL_SECONDARY_INVENTORIES   MS,
                                MTL_PARAMETERS              MP,
                                MTL_SYSTEM_ITEMS_B          MSI
                        WHERE   MS.ORGANIZATION_ID = MP.ORGANIZATION_ID
                        AND     MS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
                        AND     MS.ORGANIZATION_ID = P_ORG
                        AND     MSI.INVENTORY_ITEM_ID = P1_ITEM_ID
                        AND     UPPER(MS.SECONDARY_INVENTORY_NAME) LIKE UPPER(P_SEC_INV)
                       
                       
                     --  AND     UPPER(MS.SECONDARY_INVENTORY_NAME) LIKE '%RM'
                      
                       ;
                       --AND UPPER(MS.SECONDARY_INVENTORY_NAME) LIKE '%FG';

    CURSOR  SEC_LOC(P_ORG NUMBER,P_SEC_INV VARCHAR2,P_ITEM_ID NUMBER) IS
   
                       SELECT   MSI.INVENTORY_ITEM_ID,
                                MP.ORGANIZATION_ID,
                                MIL.INVENTORY_LOCATION_ID   SECONDARY_LOCATOR,
                                SYSDATE                     LAST_UPDATE_DATE,
                                1110                        LAST_UPDATED_BY,
                                SYSDATE                     CREATION_DATE,
                                1110                        CREATED_BY,
                                75516                       LAST_UPDATE_LOGIN
                        FROM    MTL_PARAMETERS              MP,
                                MTL_SYSTEM_ITEMS_B          MSI,
                                MTL_ITEM_LOCATIONS          MIL
                        WHERE   MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
                        AND     MSI.ORGANIZATION_ID = MIL.ORGANIZATION_ID
                        AND     MSI.ORGANIZATION_ID = P_ORG
                        AND     MSI.INVENTORY_ITEM_ID = P_ITEM_ID
                        AND     UPPER(MIL.SUBINVENTORY_CODE) LIKE UPPER(P_SEC_INV);

BEGIN
     FOR I IN C1 LOOP
      IF (L_ERR_FLAG <> 'Y') THEN
        FOR R_SUB_INV IN SUB_INV(I.ORGANIZATION_ID, I.SUBINV_CODE, I.ITEMNO) LOOP
          -- CHECK IF THE ITEM- ORGANIZATION - SUBINVENTORY COMBINATION IS ALREADY EXISTING --
          begin
            SELECT 'M'
            INTO M_EXISTS
            FROM    APPS.MTL_ITEM_SUB_INVENTORIES
            WHERE   INVENTORY_ITEM_ID   = R_SUB_INV.INVENTORY_ITEM_ID
            AND     ORGANIZATION_ID     = R_SUB_INV.ORGANIZATION_ID
            AND     UPPER(SECONDARY_INVENTORY) LIKE  UPPER(R_SUB_INV.SECONDARY_INVENTORY);
            exception
            when others then
                DBMS_OUTPUT.PUT_LINE('error -'||SQLERRM||R_SUB_INV.INVENTORY_ITEM_ID);
                M_EXISTS := 'N';
            end;

            IF (M_EXISTS <> 'M')  THEN
                BEGIN
                    INSERT INTO MTL_ITEM_SUB_INVENTORIES
                    (
                       INVENTORY_ITEM_ID,
                       ORGANIZATION_ID,
                       SECONDARY_INVENTORY,
                       LAST_UPDATE_DATE,
                       LAST_UPDATED_BY,
                       CREATION_DATE,
                       CREATED_BY,
                       LAST_UPDATE_LOGIN,
                       INVENTORY_PLANNING_CODE
                    )
                    VALUES
                    (  R_SUB_INV.INVENTORY_ITEM_ID,
                       R_SUB_INV.ORGANIZATION_ID,
                       R_SUB_INV.SECONDARY_INVENTORY,
                       R_SUB_INV.LAST_UPDATE_DATE,
                       R_SUB_INV.LAST_UPDATED_BY,
                       R_SUB_INV.CREATION_DATE,
                       R_SUB_INV.CREATED_BY,
                       R_SUB_INV.LAST_UPDATE_LOGIN,
                       R_SUB_INV.INVENTORY_PLANNING_CODE
                        );

                 FOR R_SEC_LOC IN SEC_LOC(I.ORGANIZATION_ID,R_SUB_INV.SECONDARY_INVENTORY,R_SUB_INV.INVENTORY_ITEM_ID) LOOP
                    INSERT INTO MTL_SECONDARY_LOCATORS
                        (
                            INVENTORY_ITEM_ID,
                            ORGANIZATION_ID,
                            SECONDARY_LOCATOR,
                            LAST_UPDATE_DATE,
                            LAST_UPDATED_BY,
                            CREATION_DATE,
                            CREATED_BY,
                            LAST_UPDATE_LOGIN,
                            SUBINVENTORY_CODE
                        )
                    VALUES
                        (   R_SEC_LOC.INVENTORY_ITEM_ID,
                            R_SEC_LOC.ORGANIZATION_ID,
                            R_SEC_LOC.SECONDARY_LOCATOR,
                            R_SEC_LOC.LAST_UPDATE_DATE,
                            R_SEC_LOC.LAST_UPDATED_BY,
                            R_SEC_LOC.CREATION_DATE,
                            R_SEC_LOC.CREATED_BY,
                            R_SEC_LOC.LAST_UPDATE_LOGIN,
                            R_SUB_INV.SECONDARY_INVENTORY
                        );
                       
                 END LOOP; -- LOCATOR
                COMMIT;
               
                 END;
          ELSE
              DBMS_OUTPUT.PUT_LINE('Combination Exist For Item id -'||R_SUB_INV.INVENTORY_ITEM_ID||'- Org -'||R_SUB_INV.ORGANIZATION_ID||'- SubInv -'||R_SUB_INV.SECONDARY_INVENTORY);
          END IF;

        END LOOP; -- SUB INVENTORY
        END IF;

     END LOOP; -- ITEM----ORGANIZATION

EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error -'||SQLERRM);
    FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error - '||SQLERRM);
END;
/
 

Order Management > Customers > Standard

To assign customer Location if error found "an Internal location is Already assigned to another Ship-to Address ", then need to clear existing data In R12

01. Select Location exist or not
SELECT * FROM po_Location_Associations_All p WHERE p.location_id in (select hrl.location_id from hr_locations_all hrl where hrl.location_code like '%@loc%');

If it returns any row, it means that, exist,
For deletion of internal location association, Please run following datafix:
a) Take a backup of table : FROM PO_LOCATION_ASSOCIATIONS_ALL
b) DELETE FROM PO_LOCATION_ASSOCIATIONS_ALL WHERE LOCATION_ID = &loc_id AND ORG_ID = @org_id;