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-%'
No comments:
Post a Comment