-
Notifications
You must be signed in to change notification settings - Fork 61
Expand file tree
/
Copy pathINV Cycle count listing.sql
More file actions
100 lines (99 loc) · 4.36 KB
/
INV Cycle count listing.sql
File metadata and controls
100 lines (99 loc) · 4.36 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
/*************************************************************************/
/* */
/* (c) 2010-2024 Enginatics GmbH */
/* www.enginatics.com */
/* */
/*************************************************************************/
-- Report Name: INV Cycle count listing
-- Description: Imported Oracle standard cycle count listing report
Source: Cycle count listing (XML)
Short Name: INVARCLI_XML
DB package: INV_INVARCLI_XMLP_PKG
-- Excel Examle Output: https://www.enginatics.com/example/inv-cycle-count-listing/
-- Library Link: https://www.enginatics.com/reports/inv-cycle-count-listing/
-- Run Report: https://demo.enginatics.com/
select
ood.organization_code,
ood.organization_name,
gl.currency_code,
mcch.cycle_count_header_name cycle_count_name,
mcce.subinventory subinventory,
mcce.count_list_sequence sequence,
msiv.concatenated_segments item,
msiv.description description,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
nvl(inv_project.get_locator(mcce.locator_id,mcce.organization_id),
(select milk.concatenated_segments from mtl_item_locations_kfv milk where milk.inventory_location_id = mcce.locator_id and milk.organization_id = mcce.organization_id)
) locator,
mcce.revision revision,
mcce.lot_number lot_number,
mac.abc_class_name,
mcce.count_due_date due_date,
msiv.primary_uom_code uom,
xxen_util.meaning(mcce.entry_status_code,'MTL_CC_ENTRY_STATUSES',700) status,
case
when (msiv.serial_number_control_code in (1,6) or mcch.serial_count_option=1) then
(select
nvl(sum(moqd.primary_transaction_quantity),0)
from
mtl_onhand_quantities_detail moqd
where
moqd.inventory_item_id=mcce.inventory_item_id and
moqd.organization_id=ood.organization_id and
moqd.subinventory_code=mcce.subinventory and
nvl(moqd.lot_number,'XX')=nvl(mcce.lot_number,'XX') and
nvl(moqd.revision,'XXX')=nvl(mcce.revision,'XXX') and
nvl(moqd.locator_id,-2)=nvl(mcce.locator_id,-2) and
nvl(moqd.lpn_id,-3)=nvl(mcce.parent_lpn_id,-3))
when (msiv.serial_number_control_code in (2,5) and mcch.serial_count_option>1) then
(select
nvl(sum(decode(msn.current_status,3,1,0)),0)
from
mtl_serial_numbers msn
where
msn.serial_number=nvl(mcce.serial_number,msn.serial_number) and
msn.inventory_item_id=mcce.inventory_item_id and
msn.current_organization_id=ood.organization_id and
msn.current_subinventory_code=mcce.subinventory and
nvl(msn.lot_number,'XX')=nvl(mcce.lot_number,'XX') and
nvl(msn.revision,'XXX')=nvl(mcce.revision,'XXX') and
nvl(msn.current_locator_id,-2)=nvl(mcce.locator_id,-2) and
nvl(msn.lpn_id,-3)=nvl(mcce.parent_lpn_id,-3))
end system_quantity,
case when (nvl(mcch.container_enabled_flag,-99)>0) then wlpn1.license_plate_number end outermost_license_plate_number,
case when (nvl(mcch.container_enabled_flag,-99)>0) then wlpn2.license_plate_number end parent_license_plate_number,
ccg.cost_group cost_group,
(select distinct listagg(mcsn.serial_number,', ') within group (order by mcsn.cycle_count_entry_id) from mtl_cc_serial_numbers mcsn where mcsn.cycle_count_entry_id=mcce.cycle_count_entry_id) serial_numbers
from
mtl_abc_classes mac,
mtl_system_items_vl msiv,
mtl_cycle_count_headers mcch,
mtl_cycle_count_items mcci,
mtl_cycle_count_entries mcce,
org_organization_definitions ood,
gl_ledgers gl,
cst_cost_groups ccg,
wms_license_plate_numbers wlpn1,
wms_license_plate_numbers wlpn2
where
msiv.organization_id=ood.organization_id and
1=1 and
ood.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
ood.set_of_books_id=gl.ledger_id and
mcch.organization_id=ood.organization_id and
nvl(mcch.disable_date,sysdate+1)>sysdate and
mcce.organization_id=ood.organization_id and
mac.organization_id=ood.organization_id and
mcce.inventory_item_id=mcci.inventory_item_id and
mcci.abc_class_id=mac.abc_class_id and
mcce.cycle_count_header_id=mcci.cycle_count_header_id and
mcce.cycle_count_header_id=mcch.cycle_count_header_id and
msiv.inventory_item_id=mcce.inventory_item_id and
mcce.cost_group_id=ccg.cost_group_id(+) and
mcce.outermost_lpn_id=wlpn1.lpn_id(+) and
mcce.parent_lpn_id=wlpn2.lpn_id(+)
order by
mcce.count_list_sequence,
mcce.subinventory,
msiv.concatenated_segments,
mcce.revision