<ROOT>
 <APPS_INITIALIZE_DATA>
  <USER_NAME>ENGINATICS</USER_NAME>
  <RESPONSIBILITY_KEY>SYSTEM_ADMINISTRATOR</RESPONSIBILITY_KEY>
  <APPLICATION_SHORT_NAME>SYSADMIN</APPLICATION_SHORT_NAME>
 </APPS_INITIALIZE_DATA>
<REPORTS>
<!-- loader xml for Enginatics Blitz Report: CAC Inventory to G/L Reconciliation (Restricted by Org Access) -->
 <REPORTS_ROW>
  <GUID>F564DF31388B2129E05369FB0905C646</GUID><ENABLED>Y</ENABLED>
  <SQL_TEXT>with o as (
select /*+ materialize*/
mp.organization_id,
mp.primary_cost_method,
case when mp.cost_group_accounting=1 or mp.wms_enabled_flag=&apos;Y&apos; or mp.organization_id in (select pop.organization_id from pjm_org_parameters pop) then &apos;Y&apos; end cost_group_accounting,
mp.material_account,
mp.material_overhead_account,
mp.resource_account,
mp.overhead_account,
mp.outside_processing_account,
mp.intransit_inv_account,
gl.ledger_id
from
mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units haou,
gl_ledgers gl
where
5=5 and
mp.organization_id=hoi.organization_id and
hoi.org_information_context=&apos;Accounting Information&apos; and
hoi.organization_id=haou.organization_id and
sysdate&lt;nvl(haou.date_to,sysdate+1) and
to_number(hoi.org_information1)=gl.ledger_id and
gl.ledger_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;LEDGER&apos;)
),
y as (
select /*+ materialize*/ distinct
x.ledger_id,
gcc.&amp;account_segment account
from
(
select
o.ledger_id,
decode(rowgen.column_value,1,msi.material_account,2,msi.material_overhead_account,3,msi.resource_account,4,msi.overhead_account,5,msi.outside_processing_account) account_id
from
o,
mtl_secondary_inventories msi,
table(xxen_util.rowgen(5)) rowgen
where
o.primary_cost_method=1 and
o.cost_group_accounting is null and
o.organization_id=msi.organization_id and
msi.asset_inventory=1
union all
select
o.ledger_id,
decode(rowgen.column_value,1,o.material_account,2,o.material_overhead_account,3,o.resource_account,4,o.overhead_account,5,o.outside_processing_account,6,o.intransit_inv_account) account_id
from
o,
table(xxen_util.rowgen(6)) rowgen
where
rowgen.column_value=6 or
o.primary_cost_method&lt;&gt;1 and
o.cost_group_accounting is null
union all
select
o.ledger_id,
decode(rowgen.column_value,1,ccga.material_account,2,ccga.material_overhead_account,3,ccga.resource_account,4,ccga.overhead_account,5,ccga.outside_processing_account) account_id
from
o,
cst_cost_group_accounts ccga,
table(xxen_util.rowgen(5)) rowgen
where
o.cost_group_accounting=&apos;Y&apos; and
o.organization_id=ccga.organization_id
union all
select
o.ledger_id,
decode(rowgen.column_value,1,mca.material_account,2,mca.material_overhead_account,3,mca.resource_account,4,mca.overhead_account,5,mca.outside_processing_account) account_id
from
o,
mtl_category_accounts mca,
table(xxen_util.rowgen(5)) rowgen
where
o.organization_id=mca.organization_id
union all
select
o.ledger_id,
decode(rowgen.column_value,1,wac.material_account,2,wac.material_overhead_account,3,wac.resource_account,4,wac.overhead_account,5,wac.outside_processing_account) account_id
from
o,
wip_accounting_classes wac,
table(xxen_util.rowgen(5)) rowgen
where
o.organization_id=wac.organization_id and
wac.class_type not in (4,6,7)
union all
select
o.ledger_id,
mip.intransit_inv_account
from
o,
mtl_interorg_parameters mip
where
o.organization_id in (mip.from_organization_id,mip.to_organization_id)
union all
select
o.ledger_id,
rp.receiving_account_id
from
o,
rcv_parameters rp
where
o.organization_id=rp.organization_id
) x,
gl_code_combinations gcc
where
x.account_id=gcc.code_combination_id
)
select
x.period_name,
gl.name ledger,
&amp;segment_columns
sum(x.gl_beg_balance) gl_beginning_balance,
sum(x.gl_receiving_amount) gl_receiving,
sum(x.gl_inventory_amount) gl_inventory,
sum(x.gl_wip_amount) gl_work_in_process,
sum(x.gl_payables_amount) gl_payables,
sum(x.gl_other_amount) gl_other,
sum(x.gl_end_balance) gl_ending_balance,
sum(x.receiving_value) receiving_value,
sum(x.inv_onhand_value) inventory_value,
sum(x.wip_value) wip_value,
sum(x.receiving_value+x.inv_onhand_value+x.wip_value) total_perpetual_value,
sum(x.gl_end_balance-x.receiving_value-x.inv_onhand_value-x.wip_value) difference
from
(
select
gb.period_name,
gb.ledger_id,
gb.code_combination_id,
nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0) gl_beg_balance,
0 gl_receiving_amount,
0 gl_inventory_amount,
0 gl_wip_amount,
0 gl_payables_amount,
0 gl_other_amount,
nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)+nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0) gl_end_balance,
0 receiving_value,
0 inv_onhand_value,
0 wip_value
from
gl_ledgers gl,
gl_balances gb,
gl_code_combinations gcc
where
gl.ledger_id in (select o.ledger_id from o) and
gl.ledger_category_code&lt;&gt;&apos;SECONDARY&apos; and
gl.bal_seg_value_option_code&lt;&gt;&apos;A&apos; and
gl.ledger_id=gb.ledger_id and
gb.period_name=:period_name and
gb.actual_flag=&apos;A&apos; and
gb.period_type=gl.accounted_period_type and
gb.currency_code=gl.currency_code and
gb.code_combination_id=gcc.code_combination_id and
gcc.summary_flag=&apos;N&apos; and
(gl.ledger_id,gcc.&amp;account_segment) in (select y.ledger_id,y.account from y)
union all
select
gjh.period_name,
gjh.ledger_id,
gjl.code_combination_id,
0 gl_beg_balance,
sum(case when gjh.je_source=&apos;Cost Management&apos; and gjh.je_category=&apos;Receiving&apos; then nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) else 0 end) gl_receiving_amount,
sum(case when gjh.je_source=&apos;Cost Management&apos; and gjh.je_category=&apos;Inventory&apos; then nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) else 0 end) gl_inventory_amount,
sum(case when gjh.je_source=&apos;Cost Management&apos; and gjh.je_category=&apos;WIP&apos; then nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) else 0 end) gl_wip_amount,
sum(case when gjh.je_source=&apos;Payables&apos; then nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) else 0 end) gl_payables_amount,
sum(case when gjh.je_source not in (&apos;Cost Management&apos;,&apos;Payables&apos;) then nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) else 0 end) gl_other_amount,
0 gl_end_balance,
0 receiving_value,
0 inv_onhand_value,
0 wip_value
from
gl_ledgers gl,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
where
gl.ledger_id in (select o.ledger_id from o) and
gl.ledger_category_code&lt;&gt;&apos;SECONDARY&apos; and
gl.bal_seg_value_option_code&lt;&gt;&apos;A&apos; and
gl.ledger_id=gjh.ledger_id and
gjh.period_name=:period_name and
gjh.status=&apos;P&apos; and
gjh.actual_flag=&apos;A&apos; and
gjh.je_header_id=gjl.je_header_id and
gjh.period_name=gjl.period_name and
gjl.code_combination_id=gcc.code_combination_id and
gcc.summary_flag=&apos;N&apos; and
(gl.ledger_id,gcc.&amp;account_segment) in (select y.ledger_id,y.account from y)
group by
gjh.period_name,
gjh.ledger_id,
gjl.code_combination_id
union all
select
oap.period_name,
o.ledger_id,
case
when cpcs.subinventory_code is null then o.intransit_inv_account
when ccga.material_account is not null then ccga.material_account
when o.primary_cost_method=1 then msi.material_account
else o.material_account
end code_combination_id,
0 gl_beg_balance,
0 gl_receiving_amount,
0 gl_inventory_amount,
0 gl_wip_amount,
0 gl_payables_amount,
0 gl_other_amount,
0 gl_end_balance,
0 receiving_value,
sum(cpcs.rollback_value) inv_onhand_value,
0 wip_value
from
o,
org_acct_periods oap,
cst_period_close_summary cpcs,
mtl_secondary_inventories msi,
cst_cost_group_accounts ccga
where
o.organization_id=oap.organization_id and
oap.period_name=:period_name and
oap.acct_period_id=cpcs.acct_period_id and
oap.organization_id=cpcs.organization_id and
cpcs.rollback_quantity&lt;&gt;0 and
cpcs.organization_id=msi.organization_id(+) and
cpcs.subinventory_code=msi.secondary_inventory_name(+) and
decode(o.cost_group_accounting,&apos;Y&apos;,cpcs.cost_group_id)=ccga.cost_group_id(+) and
cpcs.organization_id=ccga.organization_id(+)
group by
oap.period_name,
o.ledger_id,
case
when cpcs.subinventory_code is null then o.intransit_inv_account
when ccga.material_account is not null then ccga.material_account
when o.primary_cost_method=1 then msi.material_account
else o.material_account
end
union all
select
w.period_name,
w.ledger_id,
decode(rowgen.column_value,1,w.material_account,2,w.material_overhead_account,3,w.resource_account,4,w.outside_processing_account,5,w.overhead_account) code_combination_id,
0 gl_beg_balance,
0 gl_receiving_amount,
0 gl_inventory_amount,
0 gl_wip_amount,
0 gl_payables_amount,
0 gl_other_amount,
0 gl_end_balance,
0 receiving_value,
0 inv_onhand_value,
decode(rowgen.column_value,1,w.material_value,2,w.material_overhead_value,3,w.resource_value,4,w.outside_processing_value,5,w.overhead_value) wip_value
from
(
select
oap.period_name,
o.ledger_id,
wdj.material_account,
wdj.material_overhead_account,
wdj.resource_account,
wdj.outside_processing_account,
wdj.overhead_account,
sum(nvl(wpb.tl_scrap_in,0)+nvl(wpb.pl_material_in,0)-nvl(wpb.tl_material_out,0)-nvl(wpb.tl_scrap_out,0)-nvl(wpb.pl_material_out,0)-nvl(wpb.tl_material_var,0)-nvl(wpb.tl_scrap_var,0)-nvl(wpb.pl_material_var,0)) material_value,
sum(nvl(wpb.pl_material_overhead_in,0)-nvl(wpb.tl_material_overhead_out,0)-nvl(wpb.pl_material_overhead_out,0)-nvl(wpb.tl_material_overhead_var,0)-nvl(wpb.pl_material_overhead_var,0)) material_overhead_value,
sum(nvl(wpb.tl_resource_in,0)+nvl(wpb.pl_resource_in,0)-nvl(wpb.tl_resource_out,0)-nvl(wpb.pl_resource_out,0)-nvl(wpb.tl_resource_var,0)-nvl(wpb.pl_resource_var,0)) resource_value,
sum(nvl(wpb.tl_outside_processing_in,0)+nvl(wpb.pl_outside_processing_in,0)-nvl(wpb.tl_outside_processing_out,0)-nvl(wpb.pl_outside_processing_out,0)-nvl(wpb.tl_outside_processing_var,0)-nvl(wpb.pl_outside_processing_var,0)) outside_processing_value,
sum(nvl(wpb.tl_overhead_in,0)+nvl(wpb.pl_overhead_in,0)-nvl(wpb.tl_overhead_out,0)-nvl(wpb.pl_overhead_out,0)-nvl(wpb.tl_overhead_var,0)-nvl(wpb.pl_overhead_var,0)) overhead_value
from
o,
org_acct_periods oap,
wip_period_balances wpb,
wip_discrete_jobs wdj,
wip_accounting_classes wac
where
o.organization_id=oap.organization_id and
oap.period_name=:period_name and
oap.organization_id=wdj.organization_id and
(wdj.date_closed is null or wdj.date_closed&gt;=oap.period_start_date) and
wdj.wip_entity_id=wpb.wip_entity_id and
wpb.acct_period_id&lt;=oap.acct_period_id and
wdj.organization_id=wac.organization_id and
wdj.class_code=wac.class_code and
wac.class_type not in (4,6,7)
group by
oap.period_name,
o.ledger_id,
wdj.material_account,
wdj.material_overhead_account,
wdj.resource_account,
wdj.outside_processing_account,
wdj.overhead_account
) w,
table(xxen_util.rowgen(5)) rowgen
union all
select
oap.period_name,
o.ledger_id,
rp.receiving_account_id code_combination_id,
0 gl_beg_balance,
0 gl_receiving_amount,
0 gl_inventory_amount,
0 gl_wip_amount,
0 gl_payables_amount,
0 gl_other_amount,
0 gl_end_balance,
sum(r.amount+r.adjustment_amount) receiving_value,
0 inv_onhand_value,
0 wip_value
from
(
select
n.organization_id,
sum(n.amount) amount,
sum(n.adjustment_amount) adjustment_amount
from
(
select
rs.to_organization_id organization_id,
rs.item_id inventory_item_id,
rs.destination_type_code,
rs.po_header_id,
rs.po_line_id,
rs.po_line_location_id,
rs.po_release_id,
rs.shipment_header_id,
pda.project_id,
rsh.receipt_num,
pda.wip_entity_id,
pda.bom_resource_id,
round(rs.to_org_primary_quantity,3) quantity,
rs.amount,
0 adjustment_amount
from
(
select
ms.to_organization_id,
ms.item_id,
ms.destination_type_code,
ms.po_header_id,
ms.po_line_id,
ms.po_line_location_id,
ms.po_distribution_id,
ms.po_release_id,
ms.shipment_header_id,
ms.shipment_line_id,
ms.rcv_transaction_id,
ms.to_org_primary_quantity,
round(ms.to_org_primary_quantity*round(sum(case when nvl(rt_correct.po_unit_price,nvl(rt_deliver.po_unit_price,0))&lt;&gt;nvl(rae.unit_price,0) then nvl(plla.price_override,nvl(rae.unit_price,0)) else nvl(rae.unit_price,0) end*nvl(rae.currency_conversion_rate,1)*rae.source_doc_quantity)/sum(rae.primary_quantity),8),2) amount
from
mtl_supply ms,
po_line_locations_all plla,
(
select
x.parent_transaction_id,
x.organization_id,
x.child_transaction_id
from
(
select
rt.transaction_id parent_transaction_id,
rt.organization_id,
connect_by_root rt.transaction_id child_transaction_id,
connect_by_isleaf isleaf
from
rcv_transactions rt
connect by
prior rt.parent_transaction_id=rt.transaction_id
start with
rt.transaction_id in (select ms.rcv_transaction_id from mtl_supply ms where ms.supply_type_code=&apos;RECEIVING&apos; and ms.to_organization_id in (select o.organization_id from o)) and
nvl(rt.consigned_flag,&apos;N&apos;)=&apos;N&apos;
) x
where
x.isleaf=1
) rt,
rcv_accounting_events rae,
rcv_transactions rt_deliver,
rcv_transactions rt_correct
where
ms.rcv_transaction_id=rt.child_transaction_id and
ms.to_organization_id=rt.organization_id and
rt.parent_transaction_id=rae.rcv_transaction_id and
rt.organization_id=rae.organization_id and
ms.po_line_location_id=plla.line_location_id(+) and
ms.rcv_transaction_id=rt_deliver.parent_transaction_id(+) and
rt_deliver.transaction_type(+)=&apos;DELIVER&apos; and
rt_deliver.transaction_id=rt_correct.parent_transaction_id(+) and
rt_correct.transaction_type(+)=&apos;CORRECT&apos;
group by
ms.to_organization_id,
ms.item_id,
ms.destination_type_code,
ms.po_header_id,
ms.po_line_id,
ms.po_line_location_id,
ms.po_distribution_id,
ms.po_release_id,
ms.shipment_header_id,
ms.shipment_line_id,
ms.rcv_transaction_id,
ms.to_org_primary_quantity
) rs,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_distributions_all pda
where
rs.shipment_header_id=rsh.shipment_header_id and
rs.shipment_line_id=rsl.shipment_line_id and
rsl.source_document_code&lt;&gt;&apos;REQ&apos; and
rs.po_distribution_id=pda.po_distribution_id(+)
union all
select distinct
rae.organization_id,
rae.inventory_item_id,
pda.destination_type_code,
rae.po_header_id,
rae.po_line_id,
rae.po_line_location_id,
rsl.po_release_id,
rsh.shipment_header_id,
pda.project_id,
rsh.receipt_num,
pda.wip_entity_id,
pda.bom_resource_id,
round(case when rt.transaction_type in (&apos;RECEIVE&apos;,&apos;MATCH&apos;) or rt.transaction_type=&apos;CORRECT&apos; and rt_parent.transaction_type=&apos;RECEIVE&apos; then -rt.primary_quantity when rt.transaction_type=&apos;RETURN TO VENDOR&apos; or rt.transaction_type=&apos;CORRECT&apos; and rt_parent.transaction_type=&apos;RETURN TO VENDOR&apos; then rt.primary_quantity else 0 end,3) quantity,
round(round(decode(sign(nvl(rrsl.accounted_dr,0)-nvl(rrsl.accounted_cr,0)),1,-abs(rae.primary_quantity),-1,abs(rae.primary_quantity),rae.primary_quantity),3)*nvl(rt.po_unit_price,0)*nvl(rt.currency_conversion_rate,1)*rt.source_doc_quantity/rt.primary_quantity,2) amount,
0 adjustment_amount
from
org_acct_periods oap,
rcv_transactions rt,
rcv_transactions rt_parent,
rcv_accounting_events rae,
rcv_receiving_sub_ledger rrsl,
po_distributions_all pda,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
where
oap.period_name=:period_name and
oap.organization_id=rt.organization_id and
rt.transaction_date&gt;=oap.schedule_close_date+1 and
rt.transaction_type&lt;&gt;&apos;DELIVER&apos; and
rt.parent_transaction_id=rt_parent.transaction_id(+) and
rt.organization_id=rt_parent.organization_id(+) and
rt.transaction_id=rae.rcv_transaction_id and
rae.accounting_event_id=rrsl.accounting_event_id and
rt.transaction_id=rrsl.rcv_transaction_id and
rrsl.accounting_line_type in (&apos;Clearing&apos;,&apos;Receiving Inspection&apos;) and
rrsl.transaction_date&gt;=oap.schedule_close_date+1 and
rrsl.reference3=pda.po_distribution_id and
rt.shipment_header_id=rsh.shipment_header_id and
rt.shipment_line_id=rsl.shipment_line_id
union all
select
mmt.organization_id,
rsl.item_id inventory_item_id,
pda.destination_type_code,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rsl.po_release_id,
rsh.shipment_header_id,
pda.project_id,
rsh.receipt_num,
pda.wip_entity_id,
pda.bom_resource_id,
round(sum(mmt.primary_quantity),3) quantity,
round(sum(mmt.primary_quantity*rt.source_doc_quantity/rt.primary_quantity*nvl(rt.po_unit_price,0)*nvl(rt.currency_conversion_rate,1)),2) amount,
0 adjustment_amount
from
org_acct_periods oap,
mtl_material_transactions mmt,
rcv_parameters rp,
rcv_transactions rt,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_distributions_all pda
where
oap.period_name=:period_name and
oap.organization_id=mmt.organization_id and
mmt.transaction_date&gt;=oap.schedule_close_date+1 and
mmt.transaction_source_type_id=1 and
mmt.organization_id=rp.organization_id and
mmt.distribution_account_id=rp.receiving_account_id and
mmt.rcv_transaction_id=rt.transaction_id and
rt.transaction_date&gt;=oap.schedule_close_date+1 and
rt.shipment_header_id=rsh.shipment_header_id and
rt.shipment_line_id=rsl.shipment_line_id and
rt.po_line_location_id=pda.line_location_id and
nvl(rsl.po_distribution_id,pda.po_distribution_id)=pda.po_distribution_id
group by
rt.transaction_id,
mmt.organization_id,
rsl.item_id,
pda.destination_type_code,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rsl.po_release_id,
rsh.shipment_header_id,
pda.project_id,
rsh.receipt_num,
pda.wip_entity_id,
pda.bom_resource_id
union all
select
wta.organization_id,
rsl.item_id inventory_item_id,
pda.destination_type_code,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rsl.po_release_id,
rsh.shipment_header_id,
pda.project_id,
rsh.receipt_num,
pda.wip_entity_id,
pda.bom_resource_id,
round(case when rt.transaction_type=&apos;DELIVER&apos; or rt.transaction_type=&apos;CORRECT&apos; and rt_parent.transaction_type=&apos;DELIVER&apos; then rt.primary_quantity when rt.transaction_type=&apos;RETURN TO RECEIVING&apos; or rt.transaction_type=&apos;CORRECT&apos; and rt_parent.transaction_type in (&apos;RETURN TO RECEIVING&apos;,&apos;MATCH&apos;) then -rt.primary_quantity else 0 end,3) quantity,
round(-sum(wta.base_transaction_value),2) amount,
0 adjustment_amount
from
org_acct_periods oap,
wip_transaction_accounts wta,
rcv_parameters rp,
wip_transactions wt,
rcv_transactions rt,
rcv_transactions rt_parent,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_distributions_all pda
where
oap.period_name=:period_name and
oap.organization_id=wta.organization_id and
wta.transaction_date&gt;=oap.schedule_close_date+1 and
wta.accounting_line_type in (4,5) and
wta.organization_id=rp.organization_id and
wta.reference_account=rp.receiving_account_id and
wta.transaction_id=wt.transaction_id and
wt.transaction_date&gt;=oap.schedule_close_date+1 and
wt.rcv_transaction_id=rt.transaction_id and
wt.organization_id=rt.organization_id and
rt.transaction_date&gt;=oap.schedule_close_date+1 and
rt.parent_transaction_id=rt_parent.transaction_id(+) and
rt.organization_id=rt_parent.organization_id(+) and
rt.shipment_header_id=rsh.shipment_header_id and
rt.shipment_line_id=rsl.shipment_line_id and
rt.po_line_location_id=pda.line_location_id and
nvl(rsl.po_distribution_id,pda.po_distribution_id)=pda.po_distribution_id and
pda.destination_type_code&lt;&gt;&apos;EXPENSE&apos; and
wta.wip_entity_id=pda.wip_entity_id
group by
rt.transaction_id,
rt.transaction_type,
rt_parent.transaction_type,
rt.primary_quantity,
wta.organization_id,
rsl.item_id,
pda.destination_type_code,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rsl.po_release_id,
rsh.shipment_header_id,
pda.project_id,
rsh.receipt_num,
pda.wip_entity_id,
pda.bom_resource_id
union all
select
wcti.organization_id,
rsl.item_id inventory_item_id,
pda.destination_type_code,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rsl.po_release_id,
rsh.shipment_header_id,
pda.project_id,
rsh.receipt_num,
pda.wip_entity_id,
pda.bom_resource_id,
round(sum(wcti.primary_quantity),3) quantity,
round(sum(wcti.primary_quantity*rt.source_doc_quantity/rt.primary_quantity*nvl(rt.po_unit_price,0)*nvl(rt.currency_conversion_rate,1)),2) amount,
0 adjustment_amount
from
org_acct_periods oap,
wip_cost_txn_interface wcti,
rcv_parameters rp,
rcv_transactions rt,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_distributions_all pda
where
oap.period_name=:period_name and
oap.organization_id=wcti.organization_id and
wcti.transaction_date&gt;=oap.schedule_close_date+1 and
wcti.transaction_type=3 and
wcti.organization_id=rp.organization_id and
wcti.receiving_account_id=rp.receiving_account_id and
wcti.rcv_transaction_id=rt.transaction_id and
wcti.organization_id=rt.organization_id and
rt.transaction_date&gt;=oap.schedule_close_date+1 and
rt.shipment_header_id=rsh.shipment_header_id and
rt.shipment_line_id=rsl.shipment_line_id and
rt.po_line_location_id=pda.line_location_id and
nvl(rsl.po_distribution_id,pda.po_distribution_id)=pda.po_distribution_id and
pda.destination_type_code&lt;&gt;&apos;EXPENSE&apos; and
wcti.wip_entity_id=pda.wip_entity_id
group by
rt.transaction_id,
wcti.organization_id,
rsl.item_id,
pda.destination_type_code,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rsl.po_release_id,
rsh.shipment_header_id,
pda.project_id,
rsh.receipt_num,
pda.wip_entity_id,
pda.bom_resource_id
union all
select
rt.organization_id,
rsl.item_id inventory_item_id,
pda.destination_type_code,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rsl.po_release_id,
rsh.shipment_header_id,
pda.project_id,
rsh.receipt_num,
pda.wip_entity_id,
pda.bom_resource_id,
0 quantity,
round(-sum(nvl(rrsl.accounted_dr,0)-nvl(rrsl.accounted_cr,0)),2) amount,
0 adjustment_amount
from
org_acct_periods oap,
rcv_transactions rt,
rcv_parameters rp,
rcv_receiving_sub_ledger rrsl,
po_distributions_all pda,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
where
oap.period_name=:period_name and
oap.organization_id=rt.organization_id and
rt.transaction_date&gt;=oap.schedule_close_date+1 and
rt.transaction_type=&apos;DELIVER&apos; and
rt.organization_id=rp.organization_id and
rt.transaction_id=rrsl.rcv_transaction_id and
rrsl.accounting_line_type in (&apos;Clearing&apos;,&apos;Receiving Inspection&apos;) and
rrsl.transaction_date&gt;=oap.schedule_close_date+1 and
rrsl.code_combination_id=rp.receiving_account_id and
rrsl.reference3=pda.po_distribution_id and
rt.po_line_location_id=pda.line_location_id and
pda.destination_type_code&lt;&gt;&apos;EXPENSE&apos; and
rt.shipment_header_id=rsh.shipment_header_id and
rt.shipment_line_id=rsl.shipment_line_id
group by
rt.transaction_id,
rt.organization_id,
rsl.item_id,
pda.destination_type_code,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rsl.po_release_id,
rsh.shipment_header_id,
pda.project_id,
rsh.receipt_num,
pda.wip_entity_id,
pda.bom_resource_id
union all
select distinct
rae.organization_id,
rae.inventory_item_id,
pda.destination_type_code,
rae.po_header_id,
rae.po_line_id,
rae.po_line_location_id,
rsl.po_release_id,
rsh.shipment_header_id,
pda.project_id,
rsh.receipt_num,
pda.wip_entity_id,
pda.bom_resource_id,
0 quantity,
0 amount,
round(case when rt.transaction_type in (&apos;RECEIVE&apos;,&apos;MATCH&apos;) or rt.transaction_type=&apos;CORRECT&apos; and rt_parent.transaction_type=&apos;RECEIVE&apos; then -rt.primary_quantity when rt.transaction_type=&apos;RETURN TO VENDOR&apos; or rt.transaction_type=&apos;CORRECT&apos; and rt_parent.transaction_type=&apos;RETURN TO VENDOR&apos; then rt.primary_quantity else 0 end*
(nvl(rt_deliver.po_unit_price,0)*nvl(rt_deliver.currency_conversion_rate,1)*rt_deliver.source_doc_quantity/rt_deliver.primary_quantity-nvl(rt.po_unit_price,0)*nvl(rt.currency_conversion_rate,1)*rt.source_doc_quantity/rt.primary_quantity),2) adjustment_amount
from
org_acct_periods oap,
rcv_transactions rt,
rcv_transactions rt_parent,
rcv_transactions rt_deliver,
rcv_parameters rp,
rcv_accounting_events rae,
rcv_receiving_sub_ledger rrsl,
po_distributions_all pda,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
where
oap.period_name=:period_name and
oap.organization_id=rt.organization_id and
rt.transaction_date&gt;=oap.schedule_close_date+1 and
rt.transaction_type&lt;&gt;&apos;DELIVER&apos; and
rt.parent_transaction_id=rt_parent.transaction_id(+) and
rt.organization_id=rt_parent.organization_id(+) and
rt.transaction_id=rt_deliver.parent_transaction_id and
rt_deliver.transaction_type=&apos;DELIVER&apos; and
rt_deliver.po_unit_price&lt;&gt;rt.po_unit_price and
rt_deliver.transaction_date&gt;=oap.schedule_close_date+1 and
rt.organization_id=rp.organization_id and
rp.retroprice_adj_account_id is null and
rt.transaction_id=rae.rcv_transaction_id and
rae.accounting_event_id=rrsl.accounting_event_id and
rt.transaction_id=rrsl.rcv_transaction_id and
rrsl.accounting_line_type in (&apos;Clearing&apos;,&apos;Receiving Inspection&apos;) and
rrsl.transaction_date&gt;=oap.schedule_close_date+1 and
rrsl.reference3=pda.po_distribution_id and
rt.shipment_header_id=rsh.shipment_header_id and
rt.shipment_line_id=rsl.shipment_line_id
) n
group by
n.organization_id,
n.inventory_item_id,
n.destination_type_code,
n.po_header_id,
n.po_line_id,
n.po_line_location_id,
n.po_release_id,
n.project_id,
n.receipt_num,
n.shipment_header_id,
n.wip_entity_id,
n.bom_resource_id
having
abs(sum(nvl(n.quantity,0)+nvl(n.amount,0)))&gt;.01
) r,
o,
org_acct_periods oap,
rcv_parameters rp
where
r.organization_id=o.organization_id and
o.organization_id=oap.organization_id and
oap.period_name=:period_name and
o.organization_id=rp.organization_id
group by
oap.period_name,
o.ledger_id,
rp.receiving_account_id
) x,
gl_ledgers gl,
gl_code_combinations gcc
where
x.ledger_id=gl.ledger_id and
x.code_combination_id=gcc.code_combination_id
group by
x.period_name,
gl.name
&amp;group_by_segments
&amp;having_clause
order by
x.period_name,
gl.name
&amp;group_by_segments</SQL_TEXT>
  <VERSION_COMMENTS>Rebuilt to the Blitz Report SQL standards: one pass per value source grouped by account combination instead of fifteen segment columns per level. The receiving value uses the CAC Receiving Value (Period-End) logic (retroactive price adjustments, corrected prices of onhand receipts), so both reports agree. Inventory value is placed on the cost group material account in cost group organizations and on the organization material account in average, FIFO and LIFO organizations, the same accounts the report discovers as valuation accounts. WIP value of non-standard jobs without an assembly is no longer missing. The discovered valuation accounts include the organization intransit account, category accounts and the WIP class accounts of cost group organizations (that branch selected cost group ids as accounts), and a ledger shows only the natural accounts of its own organizations (with Ledger blank, accounts of organizations in other charts of accounts and in ledgers without access added unrelated GL rows). Inventory Value was always zero and Receiving Value carried the inventory value as well (the union branches listed the value columns in different orders). Minimum Value Difference counts the receiving amounts too. Ledger security once per organization.</VERSION_COMMENTS>
  <NUMBER_FORMAT>#,##0.00;[Red]-#,##0.00</NUMBER_FORMAT>
  <REPORT_TRANSLATIONS>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>AR</LANGUAGE>
    <REPORT_NAME>CAC Inventory to G/L Reconciliation (Restricted by Org Access)</REPORT_NAME>
    <DESCRIPTION>تقرير لمقارنة أرصدة مخزون دفتر الأستاذ العام بقيم المخزون الدائمة (بناءً على أرصدة مخزون نهاية الشهر المخزنة ، والتي تم إنشاؤها عند إغلاق فترة محاسبة المخزون).

/ * + ================================================ =============================== +
- | حقوق النشر 2010-20 Douglas Volz Consulting، Inc. |
- | كل الحقوق محفوظة. |
- | يتم منح الإذن باستخدام هذا الرمز بشرط أن يكون المؤلف الأصلي هو |
- | اعترف. لا توجد ضمانات ، صريحة أو غير مضمنة في هذا |
- | الإذن. |
- + ================================================ =============================== +
- |
- | المؤلف الأصلي: دوغلاس فولز (doug@volzconsulting.com)
- |
- | اسم البرنامج: XXX_INV_RECON_REPT.sql
- |
- | العوامل:
- | P_PERIOD_NAME - أدخل اسم الفترة التي ترغب في تسوية أرصدة لها
- | (إلزامي)
- | P_LEDGER - دفتر الأستاذ العام الذي ترغب في الإبلاغ عنه ، أدخل جميع دفاتر الأستاذ
- | رمز NULL أو٪ (معلمة اختيارية)
- |
- | وصف:
- | تقرير للتوفيق بين دفتر الأستاذ العام والمخزون وعمل العمل الدائم
- | بواسطة دفتر الأستاذ والحساب الكامل لفترة محاسبية مرغوبة.
- |
- | ==================================================== ===========================
- | لا يأخذ في الاعتبار مجموعات التكلفة ويفترض حسابات التكلفة الأولية
- | المخزون الفرعي هو نفس الحساب المادي.
- | يستخدم هذا البرنامج النصي أيضًا رمز بحث مخصص يسمى XXX_CST_GLINV_RECON_ACCOUNTS
- | كوسيلة لتحديد أرقام حسابات الجرد الصحيحة
- | ==================================================== ===========================
- |
- | تم تعديل الإصدار حسب الوصف
- | ======= =========== =============== ================== ========================
- | 1.0 24 سبتمبر 2004 ترميز دوجلاس فولز الأولي استنادًا إلى العمل السابق مع
- | النصوص والتصاميم التالية:
- | XXX_GL_RECON.sql ،
- | XXX_PERPETUAL_INV_RECON_SUM.sql ،
- | XXX_PERPETUAL_RCV_RECON_SUM.sql ،
- | XXX_PERPETUAL_WIP_RECON_SUM.sql ،
- | تسوية المخزون MD050
- | 1.1 28 يونيو 2010 دوغلاس فولز تصميم ورمز محدث للإصدار 12 ،
- | تم تغيير GL_SETS_OF_BOOKS إلى GL_LEDGERS
- | 1.2 14 نوفمبر 2010 تم تعديل Douglas Volz لعرض التكلفة SIG
- | 1.3 11 مارس 2014 قام دوجلاس فولز بتغيير مقاطع شهادة توثيق البرامج لتكون عامة وإزالتها
- | ينضم خط الإنتاج الثاني إلى gl_code_combinations
- | 1.4 07 أبريل 2014 تمت إضافة دوجلاس فولز شرط الانضمام لتجنب دفاتر الأستاذ الثانوية و
- | أضاف to_char صريحًا على الحسابات
- | ml.lookup_code لتجنب خطأ SQL &quot;رقم غير صالح&quot;.
- | 1.5 20 يوليو 2016 تمت إضافة شرط دوجلاس فولز لتجنب المجلات الموجزة
- | 1.6 18 مايو 2020 Douglas Volz تجنب منظمات المخزون المعوقين.
- + ================================================ =============================== + * /

XXX_INV_RECON_REPT_V5-20 يوليو 2016.sql</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>D</LANGUAGE>
    <REPORT_NAME>CAC Inventory to G/L Reconciliation (Restricted by Org Access)</REPORT_NAME>
    <DESCRIPTION>Bericht zum Vergleich der Hauptbuch-Bestandssalden mit den Werten der permanenten Inventur (basierend auf den gespeicherten Monatsend-Bestandssalden, die beim Abschluss der Inventur-Buchhaltungsperiode erstellt werden).

/* +=============================================================================+
-- | Copyright 2010-20 Douglas Volz Consulting, Inc.                            |
-- | Alle Rechte vorbehalten.                                                       |
-- | | Die Erlaubnis zur Verwendung dieses Codes wird erteilt, sofern der ursprüngliche Autor |
| | | gewürdigt wird.  Diese | -- | Erlaubnis beinhaltet keine ausdrücklichen oder sonstigen Garantien.
| | | Erlaubnis.                                                                |
-- +=============================================================================+
-- |
-- | Originalautor: Douglas Volz (doug@volzconsulting.com)
-- |
-- | | Programmname:  XXX_INV_RECON_REPT.sql
-- |
-- | Parameter:
-- | P_PERIOD_NAME -- Geben Sie den Namen der Periode ein, für die Sie die Salden abstimmen möchten
-- | (obligatorisch)
-- | P_LEDGER -- Hauptbuch, das Sie berichten möchten, für alle Ledger geben Sie
-- | ein NULL- oder %-Symbol (optionaler Parameter)
-- |
-- | Beschreibung:
-- | Bericht zum Abgleich von Hauptbuch und dem Inventar und WIP Perpetual
-- | nach Ledger und vollständigem Konto, für eine gewünschte Buchhaltungsperiode.
-- |
-- | ============================================================================
-- | Berücksichtigt keine Kostengruppen und geht davon aus, dass die elementaren Kostenkonten nach
-- | Unterinventar die gleichen sind wie das Materialkonto.
-- | Dieses Skript verwendet auch einen benutzerdefinierten Nachschlagecode namens XXX_CST_GLINV_RECON_ACCOUNTS
-- | als Mittel zur Bestimmung der gültigen Bestandskontonummern
-- | ============================================================================
-- |
-- | Version Geändert am Geändert von Beschreibung
-- | ======= =========== ============== =========================================
-- | 1.0 24 Sep 2004 Douglas Volz Erste Kodierung basierend auf früheren Arbeiten mit
-- | den folgenden Skripten und Entwürfen:
-- | XXX_GL_RECON.sql,
-- | XXX_PERPETUAL_INV_RECON_SUM.sql,
-- | XXX_PERPETUAL_RCV_RECON_SUM.sql, -- | XXX_PERPETUAL_RCV_RECON_SUM.sql,
-- | XXX_PERPETUAL_WIP_RECON_SUM.sql, -- | XXX_PERPETUAL_WIP_RECON_SUM.sql
-- | MD050 Bestandsabgleich
-- | 1.1 28 Jun 2010 Douglas Volz Aktualisiertes Design und Code für Release 12,
-- | GL_SETS_OF_BOOKS auf GL_LEDGERS geändert
-- | 1.2 14 Nov 2010 Douglas Volz Geändert für Cost SIG Präsentation
-- | 1.3 11 Mar 2014 Douglas Volz Änderte die COA-Segmente, um generisch zu sein und entfernte
-- | die zweite Produktlinienverbindung zu gl_code_combinations
-- | 1.4 07 Apr 2014 Douglas Volz Join-Bedingung hinzugefügt, um sekundäre Ledger zu vermeiden und
-- | ein explizites to_char auf den Konten hinzugefügt
-- | ml.lookup_code, um einen SQL-Fehler &quot;ungültige Nummer&quot; zu vermeiden.
-- | 1.5 20 Jul 2016 Douglas Volz Bedingung hinzugefügt, um Summenjournale zu vermeiden
-- | 1.6 18. Mai 2020 Douglas Volz Deaktivierte Bestandsorganisationen vermeiden.
-- +=============================================================================+*/

XXX_INV_RECON_REPT_V5-20-Jul-2016.sql</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>E</LANGUAGE>
    <REPORT_NAME>CAC Inventory to G/L Reconciliation (Restricted by Org Access)</REPORT_NAME>
    <DESCRIPTION>Informe para comparar los saldos de inventario del Libro Mayor con los valores del inventario permanente (basado en los saldos de inventario de fin de mes almacenados, generados cuando se cierra el período de contabilidad de inventario).

/* +=============================================================================+
-- Copyright 2010-20 Douglas Volz Consulting, Inc.                            |
| Todos los derechos reservados.                                                       |
-- Se autoriza el uso de este código siempre que se reconozca al autor original.
-- | reconocido.  Este permiso no incluye ninguna garantía, expresa o de otro tipo.
-- -- Permiso de uso de este código.                                                                |
-- +=============================================================================+
-- |
-- | Autor original: Douglas Volz (doug@volzconsulting.com)
-- |
-- -- Nombre del programa:  XXX_INV_RECON_REPT.sql
-- |
-- | Parámetros:
-- | P_PERIOD_NAME -- Introduzca el nombre del período para el que desea conciliar los saldos.
-- | (obligatorio)
-- | P_LEDGER -- Libro mayor que desea informar, para todos los libros mayores introduzca
-- | un símbolo NULL o % (parámetro opcional)
-- |
-- | Descripción:
-- | Informe para conciliar el Libro Mayor y el Inventario y WIP Perpetuo
-- | por Ledger y cuenta completa, para un periodo contable deseado.
-- |
-- | ============================================================================
-- | No considera los grupos de costes y asume que las cuentas de costes elementales por
-- | subinventario son las mismas que la cuenta de material.
-- | Este script también utiliza un código de búsqueda personalizado llamado XXX_CST_GLINV_RECON_ACCOUNTS
-- | como medio para determinar los números de cuenta de inventario válidos
-- | ============================================================================
-- |
-- | Versión modificada en Modificado por Descripción
-- | ======= =========== ============== =========================================
-- | 1.0 24 Sep 2004 Douglas Volz Codificación inicial basada en trabajos anteriores con
-- | los siguientes scripts y diseños:
-- | XXX_GL_RECON.sql,
-- | XXX_PERPETUAL_INV_RECON_SUM.sql,
-- | XXX_PERPETUAL_RCV_RECON_SUM.sql,
-- XXX_PERPETUAL_WIP_RECON_SUM.sql,
-- MD050 Reconciliación del inventario
-- | 1.1 28 Jun 2010 Douglas Volz Diseño y código actualizados para la versión 12,
-- | cambió GL_SETS_OF_BOOKS a GL_LEDGERS
-- | 1.2 14 Nov 2010 Douglas Volz Modificado para la presentación del SIG de costes
-- | 1.3 11 mar 2014 Douglas Volz Cambió los segmentos COA para que sean genéricos y eliminó
-- | la segunda línea de producto se une a gl_code_combinations
-- | 1.4 07 Abr 2014 Douglas Volz Añadió la condición de unión para evitar los libros mayores secundarios y
-- | añadido un to_char explícito en las cuentas
-- | ml.lookup_code para evitar un error SQL de &quot;número no válido&quot;.
-- | 1.5 20 Jul 2016 Douglas Volz Añadida condición para evitar los diarios de resumen.
-- | 1.6 18 May 2020 Douglas Volz Evitar organizaciones de inventario deshabilitadas.
-- +=============================================================================+*/

XXX_INV_RECON_REPT_V5-20-Jul-2016.sql</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>F</LANGUAGE>
    <REPORT_NAME>CAC Inventory to G/L Reconciliation (Restricted by Org Access)</REPORT_NAME>
    <DESCRIPTION>Rapport permettant de comparer les soldes d&apos;inventaire du grand livre général avec les valeurs d&apos;inventaire perpétuel (sur la base des soldes d&apos;inventaire de fin de mois stockés, générés lors de la clôture de la période comptable d&apos;inventaire).

/* +=============================================================================+
| Copyright 2010-20 Douglas Volz Consulting, Inc.                            |
| Tous droits réservés.                                                       |
-- | L&apos;autorisation d&apos;utiliser ce code est accordée à condition que l&apos;auteur original soit |
-- | reconnu.  Aucune garantie, expresse ou autre, n&apos;est incluse dans ce |
-- | permission.                                                                |
-- +=============================================================================+
-- |
-- | Auteur original : Douglas Volz (doug@volzconsulting.com)
-- |
-- | Nom du programme :  XXX_INV_RECON_REPT.sql
-- |
-- | Paramètres :
-- | P_PERIOD_NAME -- Saisissez le nom de la période pour laquelle vous souhaitez rapprocher les soldes
| (obligatoire)
| P_LEDGER -- grand livre que vous souhaitez déclarer, pour tous les grands livres, entrez
-- | un symbole NULL ou % (paramètre optionnel)
-- |
-- | Description :
| Rapport de rapprochement entre le compte général, l&apos;inventaire et l&apos;encours perpétuel
| par grand livre et compte complet, pour une période comptable souhaitée.
-- |
-- | ============================================================================
| Ne tient pas compte des groupes de coûts et suppose que les comptes de coûts élémentaires
-- | sous-inventaire sont les mêmes que le compte matériel.
-- | Ce script utilise également un code de recherche personnalisé appelé XXX_CST_GLINV_RECON_ACCOUNTS
| comme moyen de déterminer les numéros de compte d&apos;inventaire valides
-- | ============================================================================
-- |
-- | Version modifiée le Modifié par la description
-- | ======= =========== ============== =========================================
| 1.0 24 Sep 2004 Douglas Volz Codage initial basé sur des travaux antérieurs avec
-- | les scénarios et conceptions suivants :
-- | XXX_GL_RECON.sql,
| XXX_PERPETUAL_INV_RECON_SUM.sql,
| XXX_PERPETUAL_RCV_RECON_SUM.sql,
| XXX_PERPETUAL_WIP_RECON_SUM.sql,
-- MD050 Rapprochement des stocks
| 1.1 28 Jun 2010 Douglas Volz Mise à jour de la conception et du code pour la version 12,
-- | a changé GL_SETS_OF_BOOKS en GL_LEDGERS
| 1.2 14 nov. 2010 Douglas Volz modifié pour la présentation du SIG des coûts
| 1.3 11 mars 2014 Douglas Volz a modifié les segments du COA pour les rendre génériques et les supprimer
-- La deuxième ligne de produits est jointe à gl_code_combinations
| 1.4 07 avr 2014 Douglas Volz Ajout d&apos;une condition d&apos;adhésion pour éviter les grands livres secondaires et
| a ajouté un to_char explicite sur les comptes
-- | ml.lookup_code pour éviter une erreur SQL de type &quot;numéro invalide&quot;.
-- | 1.5 20 Jul 2016 Douglas Volz Ajout d&apos;une condition pour éviter les revues de synthèse
| 1.6 18 mai 2020 Douglas Volz Éviter les organisations d&apos;inventaire désactivées.
-- +=============================================================================+*/

XXX_INV_RECON_REPT_V5-20-Jul-2016.sql</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>I</LANGUAGE>
    <REPORT_NAME>CAC Inventory to G/L Reconciliation (Restricted by Org Access)</REPORT_NAME>
    <DESCRIPTION>Rapporto per confrontare i saldi d&apos;inventario della contabilità generale con i valori dell&apos;inventario perpetuo (basati sui saldi d&apos;inventario memorizzati a fine mese, generati quando il periodo contabile dell&apos;inventario è chiuso).

/* +=============================================================================+
| Copyright 2010-20 Douglas Volz Consulting, Inc.                            |
-- | Tutti i diritti riservati.                                                       |
-- | Il permesso di usare questo codice è concesso a condizione che l&apos;autore originale sia |
-- | | riconosciuto.  Nessuna garanzia, espressa o di altro tipo, è inclusa in questa | --
-- | permesso.                                                                |
-- +=============================================================================+
-- |
-- | Autore originale: Douglas Volz (doug@volzconsulting.com)
-- |
-- | Nome del programma:  XXX_INV_RECON_REPT.sql
-- |
-- | Parametri:
-- | P_PERIOD_NAME -- Inserisci il nome del periodo per il quale vuoi riconciliare i saldi
-- | (obbligatorio)
-- | P_LEDGER -- Contabilità generale che si desidera segnalare, per tutte le contabilità inserire
-- | un simbolo NULL o % (parametro opzionale)
-- |
-- | Descrizione:
-- | Report per riconciliare G/L e l&apos;inventario e WIP Perpetuo
-- | per Ledger e conto completo, per un periodo contabile desiderato.
-- |
-- | ============================================================================
-- | Non considera i gruppi di costo e assume che i conti di costo elementari per
-- | subinventario siano gli stessi del conto materiale.
-- | Questo script usa anche un codice di ricerca personalizzato chiamato XXX_CST_GLINV_RECON_ACCOUNTS
-- | come mezzo per determinare i numeri di conto dell&apos;inventario validi
-- | ============================================================================
-- |
-- | Versione modificata il Modificato da Descrizione
-- | ======= =========== ============== =========================================
-- | 1.0 24 set 2004 Douglas Volz Codifica iniziale basata su lavori precedenti con
-- | i seguenti script e progetti:
-- | XXX_GL_RECON.sql,
-- | XXX_PERPETUAL_INV_RECON_SUM.sql,
-- | XXX_PERPETUAL_RCV_RECON_SUM.sql,
-- | XXX_PERPETUAL_WIP_RECON_SUM.sql,
-- | MD050 Riconciliazione dell&apos;inventario
-- | 1.1 28 giu 2010 Douglas Volz Aggiornato il progetto e il codice per la release 12,
-- | cambiato GL_SETS_OF_BOOKS in GL_LEDGERS
-- | 1.2 14 Nov 2010 Douglas Volz Modificato per la presentazione del Cost SIG
-- | 1.3 11 Mar 2014 Douglas Volz Ha cambiato i segmenti COA per essere generici e rimosso
-- la seconda linea di prodotti da unire a gl_code_combinations
-- | 1.4 07 Apr 2014 Douglas Volz Aggiunta condizione di join per evitare ledger secondari e
-- | aggiunto un to_char esplicito sui conti
-- | ml.lookup_code per evitare un errore SQL &quot;numero non valido&quot;.
-- | 1.5 20 lug 2016 Douglas Volz Aggiunta condizione per evitare i giornali sommari
-- | 1.6 18 maggio 2020 Douglas Volz Evitate le organizzazioni di inventario disabilitate.
-- +=============================================================================+*/

XXX_INV_RECON_REPT_V5-20-Jul-2016.sql</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>JA</LANGUAGE>
    <REPORT_NAME>CAC Inventory to G/L Reconciliation (Restricted by Org Access)</REPORT_NAME>
    <DESCRIPTION>総勘定元帳の在庫残高と永久在庫値を比較するためのレポート（在庫会計期間終了時に発生する、保管されている月末在庫残高に基づく）。

/* +=============================================================================+
--｜著作権 2010-20 Douglas Volz Consulting, Inc.                            |
                                                       |
--｜このコードの使用を許可するには、原著作者が｜原著作者であることが条件となります。
-- : - - 認められています。  本製品には、明示的であるか否かにかかわらず、いかなる保証も含まれていません。
-- --｜permission.                                                                |
-- +=============================================================================+
-- |
-- --｜原著者。ダグラス・ヴォルツ (doug@volzconsulting.com)
-- |
--｜プログラム名.  XXX_INV_RECON_REPT.sql
-- |
-- | パラメータを指定します。
-- | P_PERIOD_NAME -- 残高を調整する期間名を入力します。
--｜(必須)
-- | P_LEDGER -- 報告したい総勘定元帳、すべての元帳には次のように入力します。
-- NULL または % シンボル (オプションのパラメータ)
-- |
--｜詳細はこちら。
説明: -- -- 説明: G/LとインベントリとWIPパーペチュアルを調整するための報告書。
-- --｜希望する会計期間の元帳と全勘定で。
-- |
-- | ============================================================================
-- コストグループを考慮せず、要素コストを想定している。
-- | サブインベントリーはマテリアルアカウントと同じです。
--｜このスクリプトでは、XXX_CST_GLINV_RECON_ACCOUNTSというカスタムルックアップコードも使用しています。
--｜有効なインベントリ口座番号を決定するための手段として
-- | ============================================================================
-- |
-- 説明文で変更されています。
-- | ======= =========== ============== =========================================
-- 1.0 2004年9月24日 Douglas Volz 初期コーディングは、以前の作業に基づいています。
--｜以下のスクリプトとデザインを使用します。
-- | XXX_GL_RECON.sql.
-- | XXX_PERPETUAL_INV_RECON_SUM.sql。
--｜XXX_PERPETUAL_RCV_RECON_SUM.sql。
--｜XXX_PERPETUAL_WIP_RECON_SUM.sql.
--｜MD050 棚卸資産の棚卸し
-- | 1.1 2010年06月28日 Douglas Volz リリース12のデザインとコードを更新しました。
--｜GL_SETS_OF_BOOKSをGL_LEDGERSに変更しました。
-- 1.2 2010年11月14日 Douglas Volz Cost SIGプレゼンテーション用に修正
-- | 1.3 2014年3月11日 Douglas Volz COAセグメントを一般的なものに変更し、削除しました。
--｜2番目の製品ラインはgl_code_combinationsに参加しています。
-- 1.4 2014年4月07日 Douglas Volz 二次元帳を回避するための結合条件を追加し、二次元帳を回避するための結合条件を追加しました。
-- アカウントに明示的なto_charを追加しました。
-- | ml.lookup_codeで &quot;invalid number &quot;のSQLエラーを回避するようにしました。
-- | 1.5 2016年7月20日 Douglas Volz サマリージャーナルを回避する条件を追加
--｜1.6 2020年5月18日 ダグラス・ヴォルツ 障害者インベントリー組織を避ける。
-- +=============================================================================+*/

XXX_INV_RECON_REPT_V5-20-Jul-2016.sql</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>KO</LANGUAGE>
    <REPORT_NAME>CAC Inventory to G/L Reconciliation (Restricted by Org Access)</REPORT_NAME>
    <DESCRIPTION>General Ledger 재고 잔액을 계속 기록법 값과 비교하기위한 보고서 (재고 회계 기간이 마감 될 때 생성 된 저장된 월말 재고 잔액 기준)

/ * + ============================================= ============================= +
-| 저작권 2010-20 Douglas Volz Consulting, Inc. |
-| 판권 소유. |
-| 이 코드를 사용할 수있는 권한은 원래 작성자가 |
-| 인정. 여기에는 명시 적 또는 기타 어떠한 보증도 포함되지 않습니다.
-| 허가. |
-+ ============================================= ============================= +
-|
-| 원저자 : Douglas Volz (doug@volzconsulting.com)
-|
-| 프로그램 이름 : XXX_INV_RECON_REPT.sql
-|
-| 매개 변수 :
-| P_PERIOD_NAME-잔액을 조정할 기간 명을 입력합니다.
-| (필수)
-| P_LEDGER-보고 할 총계정 원장, 모든 원장 입력
-| NULL 또는 % 기호 (선택적 매개 변수)
-|
-| 기술:
-| G / L, 재고 및 WIP 영구 조정을위한보고
-| 원하는 회계 기간 동안 원장 및 전체 계정별로.
-|
-| ================================================ ==========================
-| 비용 그룹을 고려하지 않고 요소 비용 계정을 다음과 같이 가정합니다.
-| 창고는 자재 계정과 동일합니다.
-| 이 스크립트는 XXX_CST_GLINV_RECON_ACCOUNTS라는 사용자 지정 조회 코드도 사용합니다.
-| 유효한 재고 계정 번호를 결정하는 수단으로
-| ================================================ ==========================
-|
-| 수정 된 버전 설명에 의해 수정 됨
-| ======= =========== =============================== =======================
-| 1.0 2004 년 9 월 24 일 Douglas Volz 초기 코딩
-| 다음 스크립트 및 디자인 :
-| XXX_GL_RECON.sql,
-| XXX_PERPETUAL_INV_RECON_SUM.sql,
-| XXX_PERPETUAL_RCV_RECON_SUM.sql,
-| XXX_PERPETUAL_WIP_RECON_SUM.sql,
-| MD050 재고 조정
-| 1.1 2010 년 6 월 28 일 Douglas Volz 릴리스 12의 디자인 및 코드 업데이트,
-| GL_SETS_OF_BOOKS를 GL_LEDGERS로 변경했습니다.
-| 1.2 2010 년 11 월 14 일 Douglas Volz, 비용 SIG 프레젠테이션 수정
-| 1.3 2014 년 3 월 11 일 Douglas Volz는 COA 세그먼트를 일반으로 변경하고 제거했습니다.
-| 두 번째 제품 라인은 gl_code_combinations에 연결됩니다.
-| 1.4 07 Apr 2014 Douglas Volz 보조 원장을 피하기 위해 조인 조건 추가
-| 계정에 명시적인 to_char 추가
-| ml.lookup_code는 &quot;잘못된 번호&quot;SQL 오류를 방지합니다.
-| 1.5 20 Jul 2016 Douglas Volz 요약 저널을 피하기위한 조건 추가
-| 1.6 2020 년 5 월 18 일 Douglas Volz 장애인 재고 조직을 피하십시오.
-+ ============================================== ============================= + * /

XXX_INV_RECON_REPT_V5-20-Jul-2016.sql</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>PTB</LANGUAGE>
    <REPORT_NAME>CAC Inventory to G/L Reconciliation (Restricted by Org Access)</REPORT_NAME>
    <DESCRIPTION>Relatório para comparar os saldos de inventário do General Ledger com os valores de inventário perpétuo (baseado nos saldos de inventário armazenados no final do mês, gerados quando o período contábil de inventário é encerrado).

/* +=============================================================================+
| Copyright 2010-20 Douglas Volz Consulting, Inc.                            |
| Todos os direitos reservados.                                                       |
| A permissão para usar este código é concedida desde que o autor original seja |
| reconhecido.  Nenhuma garantia, expressa ou não, está incluída nisto |.
| permissão.                                                                |
-- +=============================================================================+

-- | Autor original: Douglas Volz (doug@volzconsulting.com)

| Nome do programa:  XXX_INV_RECON_REPT.sql

| Parâmetros:
| P_PERIOD_NAME -- Insira o Nome do Período para o qual você deseja reconciliar os balanços
-- | (obrigatório)
| P_LEDGER -- Razão geral que você deseja relatar, para todos os Razões que entram
| um símbolo NULL ou % (parâmetro opcional)

-- | Descrição:
| Relatório para conciliar o G/L e o Inventário e WIP Perpétuo
| por Ledger e conta completa, para um período contábil desejado.
-- |
-- | ============================================================================
| Não considera os grupos de custos e assume as contas de custos elementares por
| subinventoria são os mesmos que a conta de material.
| Este script também usa um código de busca personalizado chamado XXX_CST_GLINV_RECON_ACCOUNTS
| como um meio de determinar os números válidos da conta de estoque
-- | ============================================================================

| Versão Modificada em Modificada por Descrição
-- | ======= =========== ============== =========================================
| 1.0 24 set 2004 Douglas Volz Codificação Inicial baseada em trabalho anterior com
| os seguintes roteiros e projetos:
| XXX_GL_RECON.sql,
| XXX_PERPETUAL_INV_RECON_SUM.sql,
| XXX_PERPETUAL_RCV_RECON_SUM.sql,
| XXX_PERPETUAL_WIP_RECON_SUM.sql,
| MD050 Reconciliação de Inventário
| 1.1 28 Jun 2010 Douglas Volz Projeto e código atualizados para a Versão 12,
| mudou GL_SETS_OF_BOOKS para GL_LEDGERS
| 1.2 14 Nov 2010 Douglas Volz Modificado para Apresentação da SIG de Custo
| 1.3 11 Mar 2014 Douglas Volz Mudou os segmentos COA para serem genéricos e removidos
| a segunda linha de produtos une-se a gl_code_combinations
| 1.4 07 Abr 2014 Douglas Volz Adicionada condição de junção para evitar livros contábeis secundários e
-- | adicionou um to_char explícito sobre as contas
| ml.lookup_code para evitar um erro SQL de &quot;número inválido&quot;.
| 1,5 20 Jul 2016 Douglas Volz Condição adicionada para evitar resumos de periódicos
| 1,6 18 de maio de 2020 Douglas Volz Evitar organizações de inventário de deficientes.
-- +=============================================================================+*/

XXX_INV_RECON_REPT_V5-20-Jul-2016.sql</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>S</LANGUAGE>
    <REPORT_NAME>CAC Inventory to G/L Reconciliation (Restricted by Org Access)</REPORT_NAME>
    <DESCRIPTION>Rapportera för att jämföra lagerbanksbalanserna med de eviga lagervärdena (baserat på de lagrade lagersaldorna i månadsskiftet, genererade när lagerbokföringsperioden är stängd).

/ * + ================================================== ================================ +
- | Upphovsrätt 2010-20 Douglas Volz Consulting, Inc. |
- | Alla rättigheter förbehållna. |
- | Tillstånd att använda den här koden beviljas förutsatt att den ursprungliga författaren är |
- | erkänd. Inga garantier, uttryckliga eller på annat sätt ingår i detta |
- | lov. |
- + ================================================= ================================ +
- |
- | Ursprunglig författare: Douglas Volz (doug@volzconsulting.com)
- |
- | Programnamn: XXX_INV_RECON_REPT.sql
- |
- | Parametrar:
- | P_PERIOD_NAME - Ange det periodnamn du vill avstämma saldon för
- | (obligatorisk)
- | P_LEDGER - huvudbok som du vill rapportera, för alla huvudböcker kommer in
- | en NULL- eller% -symbol (valfri parameter)
- |
- | Beskrivning:
- | Rapportera för att förena G / L och Inventory och WIP Perpetual
- | av Ledger och fullständigt konto för en önskad redovisningsperiod.
- |
- | ===================================================== ============================
- | Tar inte hänsyn till kostnadsgrupper och antar grundläggande kostnadsräkenskaper efter
- | underlagret är detsamma som det väsentliga kontot.
- | Det här skriptet använder också en anpassad uppslagningskod som heter XXX_CST_GLINV_RECON_ACCOUNTS
- | som ett sätt att bestämma de giltiga lagerkontonumren
- | ===================================================== ============================
- |
- | Version modifierad på modifierad av beskrivning
- | ===================================================== =========================
- | 1.0 24 sep 2004 Douglas Volz Initial Coding baserat på tidigare arbete med
- | följande skript och mönster:
- | XXX_GL_RECON.sql,
- | XXX_PERPETUAL_INV_RECON_SUM.sql,
- | XXX_PERPETUAL_RCV_RECON_SUM.sql,
- | XXX_PERPETUAL_WIP_RECON_SUM.sql,
- | MD050 Lageravstämning
- | 1.1 28 juni 2010 Douglas Volz Uppdaterad design och kod för release 12,
- | ändrade GL_SETS_OF_BOOKS till GL_LEDGERS
- | 1.2 14 nov 2010 Douglas Volz modifierad för kostnads SIG-presentation
- | 1.3 11 mars 2014 Douglas Volz Ändrade COA-segmenten så att de var generiska och avlägsnades
- | den andra produktlinjen går med till gl_code_combinations
- | 1.4 07 apr 2014 Douglas Volz Lagt till villkor för att undvika sekundära huvudböcker och
- | lagt till en uttrycklig to_char på kontona
- | ml.lookup_code för att undvika ett &quot;ogiltigt nummer&quot; SQL-fel.
- | 1.5 20 jul 2016 Douglas Volz Tillagd villkor för att undvika sammanfattande tidskrifter
- | 1.6 18 maj 2020 Douglas Volz Undvik funktionshindrade inventeringsorganisationer.
- + ================================================== ================================ + * /

XXX_INV_RECON_REPT_V5-20-Jul-2016.sql</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>TR</LANGUAGE>
    <REPORT_NAME>CAC Inventory to G/L Reconciliation (Restricted by Org Access)</REPORT_NAME>
    <DESCRIPTION>Genel Muhasebe envanter bakiyelerini sürekli envanter değerleriyle karşılaştırmak için rapor (stok muhasebe dönemi kapatıldığında oluşturulan depolanan ay sonu envanter bakiyelerine dayalı olarak).

/ * + ============================================= ============================ +
- | Telif Hakkı 2010-20 Douglas Volz Consulting, Inc. |
- | Her hakkı saklıdır. |
- | Bu kodu kullanma izni, orijinal yazarın |
- | kabul edildi. Buna açık veya başka hiçbir garanti dahil edilmemiştir |
- | izin. |
- + =============================================== ============================ +
- |
- | Orijinal Yazar: Douglas Volz (doug@volzconsulting.com)
- |
- | Program Adı: XXX_INV_RECON_REPT.sql
- |
- | Parametreler:
- | P_PERIOD_NAME - Bakiyelerini uzlaştırmak istediğiniz Dönem Adını girin
- | (zorunlu)
- | P_LEDGER - rapor etmek istediğiniz genel muhasebe, tüm defterler için girin
- | bir NULL veya% sembolü (isteğe bağlı parametre)
- |
- | Açıklama:
- | Defteri kebir ile Envanter ve Devam Eden Çalışma Süresini uzlaştırmak için rapor
- | İstenilen bir muhasebe dönemi için Ledger ve tam hesap ile.
- |
- | ================================================ ==========================
- | Maliyet gruplarını dikkate almaz ve temel maliyet hesaplarını şu şekilde varsayar:
- | alt envanter, malzeme hesabı ile aynıdır.
- | Bu komut dosyası ayrıca XXX_CST_GLINV_RECON_ACCOUNTS adlı özel bir arama kodu kullanır.
- | geçerli envanter hesap numaralarını belirlemenin bir yolu olarak
- | ================================================ ==========================
- |
- | Açıklamaya Göre Değiştirildiğinde Değiştirilen Sürüm
- | ======= =========== ============== ================== =======================
- | 1.0 24 Eyl 2004 Douglas Volz İlk Kodlama, daha önceki çalışmalara dayanmaktadır.
- | aşağıdaki komut dosyaları ve tasarımlar:
- | XXX_GL_RECON.sql,
- | XXX_PERPETUAL_INV_RECON_SUM.sql,
- | XXX_PERPETUAL_RCV_RECON_SUM.sql,
- | XXX_PERPETUAL_WIP_RECON_SUM.sql,
- | MD050 Envanter Mutabakatı
- | 1.1 28 Haziran 2010 Douglas Volz Sürüm 12&apos;nin tasarımı ve kodu güncellendi,
- | GL_SETS_OF_BOOKS, GL_LEDGERS olarak değiştirildi
- | 1.2 14 Kasım 2010 Douglas Volz Maliyet SIG Sunumu için Değiştirildi
- | 1.3 11 Mart 2014 Douglas Volz COA segmentlerini genel olacak şekilde değiştirdi ve kaldırıldı
- | ikinci ürün grubu gl_code_combinations ile birleşir
- | 1.4 07 Nis 2014 Douglas Volz İkincil defterlerden kaçınmak için birleştirme koşulu eklendi ve
- | hesaplara açık bir to_char ekledi
- | ml.lookup_code, &quot;geçersiz sayı&quot; SQL hatasını önlemek için.
- | 1.5 20 Tem 2016 Douglas Volz Özet günlüklerden kaçınmak için koşul eklendi
- | 1.6 18 Mayıs 2020 Douglas Volz Engelli envanter organizasyonlarından kaçının.
- + =============================================== ============================ + * /

XXX_INV_RECON_REPT_V5-20-Tem-2016.sql</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>US</LANGUAGE>
    <REPORT_NAME>CAC Inventory to G/L Reconciliation (Restricted by Org Access)</REPORT_NAME>
    <DESCRIPTION>For Discrete Costing, this report compares the General Ledger inventory balances with the perpetual inventory values (based on the stored month-end inventory and WIP balances, generated when the inventory accounting period is closed, plus a calculated month-end receiving value).  Inventory balances includes Receiving, Onhand Inventory (Stock), Intransit and Work in Process (WIP).  This report automatically discovers your valuation accounts based on your setups, such as the Cost Method (Standard, Average, FIFO or LIFO Costing), and also if using Project Manufacturing (PJM - Cost Group Accounting) or Warehouse Management (WMS - Cost Group Accounting) or even if using Category Accounts.  But note as maintenance work orders are normally charged to expense accounts, maintenance (EAM) work orders are not included in this report.  Also note this report does not break out the perpetual account values by cost element; it assumes the elemental cost accounts by subinventory or cost group are the same as the material account, as the stored month-end perpetual inventory balances are only stored by organization, accounting period and item, but not by cost element.

/* +=============================================================================+
-- |  Copyright 2010-23 Douglas Volz Consulting, Inc.
-- |  All rights reserved.
-- |  Permission to use this code is granted provided the original author is
-- |  acknowledged.  No warranties, express or otherwise is included in this permission.
-- +=============================================================================+
-- |
-- |  Original Author: Douglas Volz (doug@volzconsulting.com)
-- |
-- |  Program Name:  XXX_INV_RECON_REPT.sql
-- |
-- |  Parameters:
-- |  P_PERIOD_NAME      -- Enter the Period Name you wish to reconcile balances for (mandatory)
-- |  P_LEDGER           -- general ledger you wish to report, for all ledgers left this parameter blank
-- |  ============================================================================
-- |
-- |  Version Modified on Modified  by   Description
-- |  ======= =========== ============== =========================================
-- |  1.0     24 Sep 2004 Douglas Volz   Initial Coding based on earlier work with
-- |                                     the following scripts and designs:
-- |                                        XXX_GL_RECON.sql,
-- |                                        XXX_PERPETUAL_INV_RECON_SUM.sql,
-- |                                        XXX_PERPETUAL_RCV_RECON_SUM.sql,
-- |                                        XXX_PERPETUAL_WIP_RECON_SUM.sql,
-- |                                        MD050 Inventory Reconciliation
-- |  1.1     28 Jun 2010 Douglas Volz   Updated design and code for Release 12,
-- |                                     changed GL_SETS_OF_BOOKS to GL_LEDGERS
-- |  1.2     14 Nov 2010 Douglas Volz   Modified for Cost SIG Presentation
-- |  1.3     11 Mar 2014 Douglas Volz   Changed the COA segments to be generic and removed
-- |                                     the second product line join to gl_code_combinations
-- |  1.4     07 Apr 2014 Douglas Volz   Added join condition to avoid secondary ledgers and
-- |                                     added an explicit to_char on the accounts
-- |                                     ml.lookup_code to avoid an &quot;invalid number&quot; SQL error.
-- |  1.5     20 Jul 2016 Douglas Volz   Added condition to avoid summary journals
-- |  1.6     18 May 2020 Douglas Volz   Avoid disabled inventory organizations.
-- |  1.7     07 Dec 2020 Douglas Volz/Andy Haack   Only report inventory organization ledgers.  Initial Blitz version.
-- |  1.8     22 Feb 2023 Douglas Volz   Add in Receiving Value, fixes for Category Accounting.
-- |  1.9     24 Oct 2026 Andy Haack    Rebuilt to the Blitz Report SQL standards.
-- +=============================================================================+*/
</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>ZHS</LANGUAGE>
    <REPORT_NAME>CAC Inventory to G/L Reconciliation (Restricted by Org Access)</REPORT_NAME>
    <DESCRIPTION>报告将总账库存余额与永续库存值进行比较（根据存储的月末库存余额，在库存会计期结束时生成）。

/* +=============================================================================+
-- 2010-20道格拉斯-沃尔兹咨询公司版权所有。                            |
-- 保留所有权利。                                                       |
-- 允许使用该代码，但前提是原作者必须是｜。
-- 认可。  不包含任何明示或暗示的保证。
-- 允许。                                                                |
-- +=============================================================================+
-- |
-- 原作者：Douglas Volz ()Douglas Volz (doug@volzconsulting.com)
-- |
-- 程序名称：XXX_INV_RECON_REPT.sql  XXX_INV_RECON_REPT.sql。
-- |
-- 参数。
参数： -- P_PERIOD_NAME -- 输入您希望调节余额的期间名称。
-- (强制性)
-- P_LEDGER -- 您希望报告的总分类账，所有分类账都输入
-- NULL或%符号(可选参数)
-- |
-- 说明。
说明： -- -- 核对G/L和库存及WIP Perpetual的报告。
-- 按分类账和全额账目计算，所需会计期间。
-- |
-- | ============================================================================
-- 不考虑成本组别，并假定要素成本账户按以下方式计算：
-- 子库存与物料账户相同。
-- 这个脚本还使用了一个名为XXX_CST_GLINV_RECON_ACCOUNTS的自定义查询代码。
-- 作为确定有效存货帐号的一种手段。
-- | ============================================================================
-- |

-- | ======= =========== ============== =========================================
-- 1.0 2004年9月24日 Douglas Volz 根据之前的工作进行了初步编码。
-- 以下脚本和设计。
-- XXX_GL_RECON.sql。
-- | XXX_PETUAL_INV_RECON_SUM.sql。
-- | XXX_PETUAL_RCV_RECON_SUM.sql。
-- | XXX_PETUAL_WIP_RECON_SUM.sql。
-- MD050库存核对
| 1.1 2010年6月28日 Douglas Volz 更新了12版的设计和代码。
-- 将GL_SETS_OF_BOOKS改为GL_LEDGERS。
-- | 1.2 2010年11月14日 Douglas Volz 修改后用于成本组的演示。
-- 1.3 2014年3月11日 Douglas Volz 将COA段改为通用段，并将其删除。
-- 第二条产品线加入gl_code_combinations。
-- | 1.4 2014年4月7日 Douglas Volz 增加了加入条件，以避免二级分类账和。
-- 在账户上增加了一个明确的to_char。

-- 1.5 2016年7月20日 Douglas Volz 增加了避免摘要期刊的条件。
-- | 1.6 2020年5月18日 Douglas Volz 避免残疾库存组织。
-- +=============================================================================+*/

XXX_INV_RECON_REPT_V5-20-Jul-2016.sql</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
  </REPORT_TRANSLATIONS>
  <CATEGORY_ASSIGNMENTS>
   <CATEGORY_ASSIGNMENTS_ROW>
    <CATEGORY>Enginatics</CATEGORY>
   </CATEGORY_ASSIGNMENTS_ROW>
   <CATEGORY_ASSIGNMENTS_ROW>
    <CATEGORY>R12 only</CATEGORY>
   </CATEGORY_ASSIGNMENTS_ROW>
  </CATEGORY_ASSIGNMENTS>
  <ANCHORS>
   <ANCHORS_ROW>
    <ANCHOR>&amp;account_segment</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>&amp;group_by_segments</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>&amp;having_clause</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>&amp;segment_columns</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>5=5</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>:period_name</ANCHOR>
   </ANCHORS_ROW>
  </ANCHORS>
  <PARAMETERS>
   <PARAMETERS_ROW>
    <SORT_ORDER>1</SORT_ORDER>
    <DISPLAY_SEQUENCE>-30</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;account_segment</ANCHOR>
    <SQL_TEXT>select
fsav.application_column_name
from
fnd_segment_attribute_values fsav,
fnd_id_flex_structures_vl fifsv
where
fsav.application_id=101 and
fsav.id_flex_code=&apos;GL#&apos; and
fsav.segment_attribute_type=&apos;GL_ACCOUNT&apos; and
fsav.attribute_value=&apos;Y&apos; and
fifsv.id_flex_structure_name=:chart_of_accounts and
fsav.application_id=fifsv.application_id and
fsav.id_flex_code=fifsv.id_flex_code and
fsav.id_flex_num=fifsv.id_flex_num</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>GL Chart of Accounts</LOV_NAME>
    <LOV_GUID>8E2FF36EDEE379D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
fifsv.id_flex_structure_name value,
fifsv.id_flex_structure_code description
from
fnd_id_flex_structures_vl fifsv
where
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.application_id=101 and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.ledger_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;LEDGER&apos;))
order by
fifsv.id_flex_structure_name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>select
fifsv.id_flex_structure_name
from
fnd_id_flex_structures_vl fifsv
where
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.application_id=101 and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.ledger_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;LEDGER&apos;)) and
rownum=1</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Chart of Accounts</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>2</SORT_ORDER>
    <ANCHOR>&amp;group_by_segments</ANCHOR>
    <SQL_TEXT>select
&apos;,gcc.&apos;||lower(fifs.application_column_name) text
from
fnd_id_flex_segments fifs
where
fifs.application_id=101 and
fifs.id_flex_code=&apos;GL#&apos; and
fifs.enabled_flag=&apos;Y&apos; and
fifs.id_flex_num=(select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code=&apos;GL#&apos; and fifsv.id_flex_structure_name=:chart_of_accounts)
order by
fifs.segment_num</SQL_TEXT>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Chart of Accounts</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>3</SORT_ORDER>
    <ANCHOR>&amp;segment_columns</ANCHOR>
    <SQL_TEXT>select
&apos;gcc.&apos;||lower(fifsv.application_column_name)||&apos; &quot;&apos;||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||&apos;&quot;,&apos; text
from
(select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv
where
fifsv.application_id=101 and
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.enabled_flag=&apos;Y&apos; and
fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code=&apos;GL#&apos; and fifsv0.id_flex_structure_name=:chart_of_accounts)
order by
fifsv.segment_num</SQL_TEXT>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Chart of Accounts</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>4</SORT_ORDER>
    <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
    <ANCHOR>:period_name</ANCHOR>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Period (closed and summarized)</LOV_NAME>
    <LOV_GUID>91E8680353217CCEE053BB6B635868C2</LOV_GUID>
    <LOV_QUERY_DSP>select distinct
oap.period_name value,
max(oap.period_year||&apos;-&apos;||oap.period_num||&apos;, &apos;||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,&apos;P&apos;,2,&apos;N&apos;,decode(oap.summarized_flag,&apos;N&apos;,65,66),4),3),&apos;MTL_ACCT_PERIOD_STATUS&apos;,700)||&apos; (&apos;||oap.period_start_date||&apos; - &apos;||oap.schedule_close_date||&apos;)&apos;) over (partition by oap.period_name) description,
max(oap.period_start_date) over (partition by oap.period_name) period_start_date,
max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num
from
gl_ledgers gl,
org_organization_definitions ood,
mtl_parameters mp,
(select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap
where
oap.period_close_date is not null and
oap.summarized_flag=&apos;Y&apos; and
oap.period_start_date&lt;=sysdate and
(:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)=&apos;Y&apos;) and
(:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)=&apos;Y&apos;) and
ood.organization_id=mp.organization_id and
mp.organization_id&lt;&gt;mp.master_organization_id and 
gl.period_set_name=oap.period_set_name and
gl.ledger_id=ood.set_of_books_id and
ood.organization_id=oap.organization_id
order by
period_start_date desc,
effective_period_num desc,
oap.period_name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>select distinct oap.period_name value
from org_acct_periods oap,
 (select max(oap.schedule_close_date) default_date
  from org_acct_periods oap,
  org_organization_definitions ood
  where ood.organization_id = oap.organization_id
  and (oap.period_close_date is not null and oap.summarized_flag = &apos;Y&apos;)
  and nvl(ood.disable_date, sysdate + 1) &gt; sysdate) max
where oap.schedule_close_date = max.default_date
and rownum = 1</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Period Name (Closed)</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>5</SORT_ORDER>
    <DISPLAY_SEQUENCE>20</DISPLAY_SEQUENCE>
    <ANCHOR>5=5</ANCHOR>
    <SQL_TEXT>gl.name=:ledger</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>GL Ledger</LOV_NAME>
    <LOV_GUID>8E2FF36EDEB879D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
gl.name value,
fifsv.id_flex_structure_name||&apos;: &apos;||decode(gl.ledger_category_code,&apos;NONE&apos;,xxen_util.meaning(gl.object_type_code,&apos;LEDGERS&apos;,101),xxen_util.meaning(gl.ledger_category_code,&apos;GL_ASF_LEDGER_CATEGORY&apos;,101))||&apos;: &apos;||gl.description description
from
gl_ledgers gl,
fnd_id_flex_structures_vl fifsv
where
(:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,&apos;GL_ASF_LEDGER_CATEGORY&apos;,101,&apos;Y&apos;)) and
(:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)=&apos;Y&apos;) and
gl.ledger_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;LEDGER&apos;) and
gl.chart_of_accounts_id=fifsv.id_flex_num and
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.application_id=101
order by
fifsv.id_flex_structure_name,
decode(gl.ledger_category_code,&apos;PRIMARY&apos;,1,&apos;SECONDARY&apos;,2,&apos;ALC&apos;,3,&apos;NONE&apos;,4),
gl.name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>coalesce(xxen_util.default_ledger,xxen_util.previous_parameter_value(:parameter_id))</DEFAULT_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Ledger</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>6</SORT_ORDER>
    <DISPLAY_SEQUENCE>30</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;having_clause</ANCHOR>
    <SQL_TEXT>having sum(abs(x.gl_beg_balance)+abs(x.gl_end_balance)+abs(x.gl_receiving_amount)+abs(x.gl_inventory_amount)+abs(x.gl_payables_amount)+abs(x.gl_wip_amount)+abs(x.gl_other_amount)+abs(x.receiving_value)+abs(x.inv_onhand_value)+abs(x.wip_value))&gt;=:min_value_difference</SQL_TEXT>
    <PARAMETER_TYPE_DSP>Number</PARAMETER_TYPE_DSP>
    <DEFAULT_VALUE>0</DEFAULT_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Minimum Value Difference</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
  </PARAMETERS>
  <TEMPLATES>
  </TEMPLATES>
  <DEFAULT_TEMPLATES>
  </DEFAULT_TEMPLATES>
  <UPLOAD_COLUMNS>
  </UPLOAD_COLUMNS>
  <UPLOAD_PARAMETERS>
  </UPLOAD_PARAMETERS>
  <UPLOAD_SQLS>
  </UPLOAD_SQLS>
 </REPORTS_ROW>
</REPORTS>
</ROOT>
