/*库存会计期间*/
SELECT STATUS,
PERIOD_NAME,
PERIOD_NUMBER,
PERIOD_YEAR,
START_DATE,
END_DATE,
CLOSE_DATE,
REC_TYPE,
ORGANIZATION_ID
FROM ORG_ACCT_PERIODS_V
WHERE ((rec_type = 'ORG_PERIOD'))
order by END_DATE desc
/* 库存组织列表*/
select a.organization_id, a.name
FROM hr_all_organization_units a
where a.organization_id > 82
/*事物处理活动列表*/
select *
from mfg_lookups
where lookup_type = 'MTL_TRANSACTION_ACTION'
ORDER BY LOOKUP_CODE
/*事物处理来源类型列表*/
SELECT TRANSACTION_SOURCE_TYPE_NAME,
DESCRIPTION,
VALIDATED_FLAG,
TRANSACTION_SOURCE_TYPE_ID,
USER_DEFINED_FLAG,
DISABLE_DATE
FROM MTL_TXN_SOURCE_TYPES
WHERE USER_DEFINED_FLAG = 'N'
order by transaction_source_type_name
/*接收事物处理*/
SELECT t.po_header_id,
t.po_line_id,
t.po_release_id,
SUM(DECODE(t.transaction_type,
'DELIVER',
t.quantity,
'RETURN TO RECEIVING',
DECODE(t.destination_type_code,
'INVENTORY',
-t.quantity,
'EXPENSE',
-t.quantity,
0),
'CORRECT',
DECODE(t.destination_type_code,
'INVENTORY',
t.quantity,
'EXPENSE',
t.quantity,
0),
0)) deliver_qtya,
SUM(t.po_unit_price * t.currency_conversion_rate *
DECODE(t.transaction_type,
'DELIVER',
t.quantity,
'RETURN TO RECEIVING',
DECODE(t.destination_type_code,
'INVENTORY',
-t.quantity,
'EXPENSE',
-t.quantity,
0),
'CORRECT',
DECODE(t.destination_type_code,
'INVENTORY',
t.quantity,
'EXPENSE',
t.quantity,
0),
0)) deliver_amount,
SUM(DECODE(t.transaction_type,
'RECEIVE',
t.quantity,
'RETURN TO VENDOR',
-t.quantity,
'CORRECT',
DECODE(t.destination_type_code, 'RECEIVING', t.quantity, 0),
0)) receive_qtya,
SUM(t.po_unit_price * t.currency_conversion_rate *
DECODE(t.transaction_type,
'RECEIVE',
t.quantity,
'RETURN TO VENDOR',
-t.quantity,
'CORRECT',
DECODE(t.destination_type_code, 'RECEIVING', t.quantity, 0),
0)) receive_amount,
t.unit_of_measure,
rsl.item_id,
rsl.item_revision,
rsl.item_description,
g.period_year,
g.period_number,
rsl.from_organization_id
FROM po.rcv_transactions t,
apps.org_acct_periods_v g,
apps.rcv_shipment_lines rsl
WHERE (t.transaction_type = 'DELIVER' OR
t.transaction_type = 'RETURN TO RECEIVING' OR
t.transaction_type = 'CORRECT' OR t.transaction_type = 'RECEIVE' OR
t.transaction_type = 'RETURN TO VENDOR')
AND t.organization_id = g.organization_id
AND TO_DATE(TO_CHAR(t.transaction_date, 'YYYY-MM-DD'), 'YYYY-MM-DD') BETWEEN
g.start_date AND g.end_date
AND g.rec_type = 'ORG_PERIOD'
AND rsl.shipment_line_id = t.shipment_line_id
GROUP BY t.po_header_id,
t.po_line_id,
t.po_release_id,
t.unit_of_measure,
rsl.item_id,
rsl.item_revision,
rsl.item_description,
g.period_year,
g.period_number,
rsl.from_organization_id
/*接收区数量金额*/
select
/* commented and added following line for 2245286
nvl( PPP.costing_group_id, 1 ) cost_group_id, */
nvl(PPP.costing_group_id, MP.default_cost_group_id) cost_group_id,
MS.item_id inventory_item_id,
msi.description,
msi.segment1,
RCV.po_unit_price item_cost,
MS.to_org_primary_quantity RCVQTY,
MS.to_org_primary_quantity *
(RCV.po_unit_price + CSTPPACQ.get_rcv_tax(RCV.transaction_id)) *
nvl(decode(nvl(POLL.match_option, 'P'),
'P',
CSTPPACQ.get_po_rate(RCV.transaction_id),
'R',
RCV.currency_conversion_rate),
1) * (RCV.source_doc_quantity / RCV.primary_quantity) RCVVAL,
RCV.po_unit_price,
CSTPPACQ.get_rcv_tax(RCV.transaction_id) dddd,
POLL.match_option,
CSTPPACQ.get_po_rate(RCV.transaction_id) fffff,
RCV.currency_conversion_rate,
rcv.currency_code,
rcv.currency_conversion_type,
RCV.source_doc_quantity,
RCV.primary_quantity,
CIC.cost_type_id,
CIC.inventory_asset_flag
from mtl_supply MS,
cst_item_costs CIC,
rcv_transactions RCV,
po_line_locations_all POLL,
po_lines_all POL,
pjm_project_parameters PPP,
mtl_parameters MP,
inv.mtl_system_items_b MSI /* added for 2245286 */
where MS.supply_type_code = 'RECEIVING'
AND CIC.organization_id = MS.to_organization_id
AND CIC.inventory_item_id(+) = MS.item_id
AND RCV.transaction_id = MS.rcv_transaction_id
AND RCV.source_document_code not in ('INVENTORY, REQ')
AND POLL.line_location_id = RCV.po_line_location_id
AND POL.po_line_id = RCV.po_line_id
AND PPP.project_id(+) = POL.project_id
and MS.to_organization_id = &P_ORG_ID
AND MP.organization_id = &P_ORG_ID
and ms.item_id = msi.inventory_item_id
order by msi.segment1
/*接收单列表*/
SELECT rt.transaction_id,
rt.transaction_type rt_transaction_type,
rt.quantity rt_quantity,
rt.quantity_billed rt_quantity_billed,
rt.inspection_status_code rt_inspection_status_code,
rt.inspection_quality_code rt_inspection_quality_code,
rt.destination_type_code rt_destination_type_code,
poh.po_header_id,
poh.segment1,
pol.line_num,
pol.line_type_id,
pol.item_description,
pol.unit_meas_lookup_code,
pol.list_price_per_unit,
pol.unit_price,
pol.quantity,
pol.qty_rcv_tolerance,
pol.closed_code,
rsh.receipt_num,
rsl.line_num,
rsl.quantity_shipped,
rsl.quantity_received,
rsl.shipment_line_status_code,
rsl.source_document_code,
rsl.destination_type_code,
rsl.asn_line_flag
FROM PO.rcv_transactions rt,
PO.rcv_shipment_headers rsh,
PO.rcv_shipment_lines rsl,
PO.po_headers_all poh,
PO.po_lines_all pol
WHERE rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND poh.po_header_id(+) = rt.po_header_id
AND pol.po_line_id(+) = rt.po_line_id
AND EXISTS (SELECT *
FROM APPS.po_lookup_codes plc1
WHERE plc1.lookup_type = 'RCV TRANSACTION TYPE'
AND rt.transaction_type = plc1.lookup_code)
AND EXISTS (SELECT *
FROM APPS.po_lookup_codes plc1
WHERE plc1.lookup_type = 'SHIPMENT SOURCE TYPE'
AND rsh.receipt_source_code = plc1.lookup_code)
AND EXISTS (SELECT *
FROM APPS.po_lookup_codes plc1
WHERE plc1.lookup_type = 'RCV DESTINATION TYPE'
AND rt.destination_type_code = plc1.lookup_code)
AND EXISTS
(SELECT *
FROM APPS.po_lookup_codes plc1
WHERE plc1.lookup_type = 'SHIPMENT SOURCE DOCUMENT TYPE'
AND rt.source_document_code = plc1.lookup_code)
/*从总帐追溯到接收*/
SELECT je_header_id,
je_line_num,
trx_class_name,
trx_type_name,
trx_number_displayed,
trx_date,
comments,
doc_sequence_name,
doc_sequence_value,
acct_line_type_name,
currency_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
taxable_entered_dr,
taxable_entered_cr,
taxable_accounted_dr,
taxable_accounted_cr,
currency_conversion_date,
currency_user_conversion_type,
currency_conversion_rate,
third_party_name,
third_party_number,
third_party_sub_name,
accounting_date,
gl_transfer_status_name,
transfer_system_name,
gl_transfer_from_to_name,
accounting_complete_name,
ae_line_reference,
tax_code,
accounting_rule_name,
tax_exempt_number,
trx_line_type_name,
trx_line_number,
trx_detail_line_number,
trx_quantity,
sales_order_number,
salesrep_name,
tax_rate,
unit_selling_price,
trx_uom,
trx_source_name,
asset_number,
asset_description,
asset_book_type_code,
accounting_event_number,
accounting_event_type,
accounting_event_type_name,
accounting_line_number,
aeh_accounting_error_name,
ael_accounting_error_name,
user_je_category_name,
transfer_status_detail_name,
application_date,
applied_to_trx_hdr_currency,
applied_to_trx_hdr_date,
applied_to_trx_hdr_id,
applied_to_trx_hdr_number_c,
applied_to_trx_hdr_number_disp,
applied_to_trx_hdr_table,
applied_to_trx_line_number,
applied_to_trx_line_type,
applied_to_trx_line_type_name,
ar_activity_name,
ar_adjustment_creation_type,
ar_adjustment_type,
bank_account_name,
bank_statement_doc_seq_id,
bank_statement_doc_seq_name,
bank_statement_doc_seq_value,
bank_statement_line_number,
bank_statement_number,
bom_department_code,
bom_department_name,
chargeback_number,
cost_element_id,
cost_element_name,
distribution_set_name,
gl_batch_id,
inventory_item_locator_id,
inventory_item_locator_name,
inventory_item_revision,
item_description,
inventory_organization_code,
inventory_organization_id,
cost_type_id,
cost_type_name,
mfg_operation_seq_num,
payment_cleared_date,
payment_date,
payment_deposit_date,
payment_number,
payment_recon_currency,
po_line_num,
po_order_number,
po_order_release_num,
price_override,
po_order_shipment_num,
po_order_distribution_num,
po_order_type,
rcv_receipt_num,
rcv_shipment_header_id,
reversal_comments,
reversal_date,
subinventory,
trx_hdr_currency,
trx_reason_name,
trx_source_type_id,
trx_source_type_name,
unit_cost,
unit_price,
wip_assembly,
wip_basis,
wip_flow_schedule_number,
wip_job_id,
wip_job_name,
wip_line_code,
wip_line_id,
wip_resource_id,
wip_resource_name,
wip_resource_seq_num,
pa_employee_num,
pa_employee_name,
pa_supplier_num,
pa_supplier_name,
pa_project_num,
pa_project_name,
pa_task_num,
pa_task_name,
pa_ei_ev_org,
pa_nl_resource_org,
pa_nl_resource,
pa_ei_ev_type,
pa_event_num,
pa_quantity,
pa_uom,
pa_period_date,
pa_gl_transfer_date,
pa_customer_number,
pa_customer_name,
pa_agreement_num,
pa_accrue_thru_date,
pa_dri_number,
pa_trans_id,
pa_line_number,
prvdr_organization_name,
recvr_organization_name,
prvdr_ou_name,
recvr_ou_name,
cc_type_name,
cc_proc_method_name,
application_id,
asset_id,
asset_category_id,
asset_key_ccid,
code_combination_id,
inventory_item_id,
set_of_books_id,
org_id,
trx_class,
trx_type_n,
trx_type_c,
trx_number_n,
trx_number_c,
doc_sequence_id,
trx_hdr_table,
trx_hdr_id,
acct_line_type,
currency_conversion_type,
third_party_type,
third_party_id,
third_party_sub_id,
gl_transfer_status,
source_table,
source_id,
ael_table,
ael_id,
aeh_accounting_error_code,
ael_accounting_error_code,
je_category,
trx_line_type,
trx_source_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
pa_project_id,
pa_view_labor_cost
FROM xla_po_ael_gl_v xla_ael_gl_v
WHERE je_header_id = 1590
AND je_line_num = 419
ORDER BY trx_date, rcv_receipt_num, trx_type_name
/*从总帐追溯到库存*/
SELECT je_header_id,
je_line_num,
trx_class_name,
trx_type_name,
trx_number_displayed,
trx_date,
comments,
doc_sequence_name,
doc_sequence_value,
acct_line_type_name,
currency_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
taxable_entered_dr,
taxable_entered_cr,
taxable_accounted_dr,
taxable_accounted_cr,
currency_conversion_date,
currency_user_conversion_type,
currency_conversion_rate,
third_party_name,
third_party_number,
third_party_sub_name,
accounting_date,
gl_transfer_status_name,
transfer_system_name,
gl_transfer_from_to_name,
accounting_complete_name,
ae_line_reference,
tax_code,
accounting_rule_name,
tax_exempt_number,
trx_line_type_name,
trx_line_number,
trx_detail_line_number,
trx_quantity,
sales_order_number,
salesrep_name,
tax_rate,
unit_selling_price,
trx_uom,
trx_source_name,
asset_number,
asset_description,
asset_book_type_code,
accounting_event_number,
accounting_event_type,
accounting_event_type_name,
accounting_line_number,
aeh_accounting_error_name,
ael_accounting_error_name,
user_je_category_name,
transfer_status_detail_name,
application_date,
applied_to_trx_hdr_currency,
applied_to_trx_hdr_date,
applied_to_trx_hdr_id,
applied_to_trx_hdr_number_c,
applied_to_trx_hdr_number_disp,
applied_to_trx_hdr_table,
applied_to_trx_line_number,
applied_to_trx_line_type,
applied_to_trx_line_type_name,
ar_activity_name,
ar_adjustment_creation_type,
ar_adjustment_type,
bank_account_name,
bank_statement_doc_seq_id,
bank_statement_doc_seq_name,
bank_statement_doc_seq_value,
bank_statement_line_number,
bank_statement_number,
bom_department_code,
bom_department_name,
chargeback_number,
cost_element_id,
cost_element_name,
distribution_set_name,
gl_batch_id,
inventory_item_locator_id,
inventory_item_locator_name,
inventory_item_revision,
item_description,
inventory_organization_code,
inventory_organization_id,
cost_type_id,
cost_type_name,
mfg_operation_seq_num,
payment_cleared_date,
payment_date,
payment_deposit_date,
payment_number,
payment_recon_currency,
po_line_num,
po_order_number,
po_order_release_num,
price_override,
po_order_shipment_num,
po_order_distribution_num,
po_order_type,
rcv_receipt_num,
rcv_shipment_header_id,
reversal_comments,
reversal_date,
subinventory,
trx_hdr_currency,
trx_reason_name,
trx_source_type_id,
trx_source_type_name,
unit_cost,
unit_price,
wip_assembly,
wip_basis,
wip_flow_schedule_number,
wip_job_id,
wip_job_name,
wip_line_code,
wip_line_id,
wip_resource_id,
wip_resource_name,
wip_resource_seq_num,
pa_employee_num,
pa_employee_name,
pa_supplier_num,
pa_supplier_name,
pa_project_num,
pa_project_name,
pa_task_num,
pa_task_name,
pa_ei_ev_org,
pa_nl_resource_org,
pa_nl_resource,
pa_ei_ev_type,
pa_event_num,
pa_quantity,
pa_uom,
pa_period_date,
pa_gl_transfer_date,
pa_customer_number,
pa_customer_name,
pa_agreement_num,
pa_accrue_thru_date,
pa_dri_number,
pa_trans_id,
pa_line_number,
prvdr_organization_name,
recvr_organization_name,
prvdr_ou_name,
recvr_ou_name,
cc_type_name,
cc_proc_method_name,
application_id,
asset_id,
asset_category_id,
asset_key_ccid,
code_combination_id,
inventory_item_id,
set_of_books_id,
org_id,
trx_class,
trx_type_n,
trx_type_c,
trx_number_n,
trx_number_c,
doc_sequence_id,
trx_hdr_table,
trx_hdr_id,
acct_line_type,
currency_conversion_type,
third_party_type,
third_party_id,
third_party_sub_id,
gl_transfer_status,
source_table,
source_id,
ael_table,
ael_id,
aeh_accounting_error_code,
ael_accounting_error_code,
je_category,
trx_line_type,
trx_source_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
pa_project_id,
pa_view_labor_cost
FROM xla_inv_ael_gl_v xla_ael_gl_v
WHERE je_header_id = 1573
AND je_line_num = 1205
ORDER BY trx_date,
trx_number_displayed,
acct_line_type_name,
cost_element_name
/** 从总帐追溯到发票 */
SELECT je_header_id,
je_line_num,
trx_class_name,
trx_type_name,
trx_number_displayed,
trx_date,
comments,
doc_sequence_name,
doc_sequence_value,
acct_line_type_name,
currency_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
taxable_entered_dr,
taxable_entered_cr,
taxable_accounted_dr,
taxable_accounted_cr,
currency_conversion_date,
currency_user_conversion_type,
currency_conversion_rate,
third_party_name,
third_party_number,
third_party_sub_name,
accounting_date,
gl_transfer_status_name,
transfer_system_name,
gl_transfer_from_to_name,
accounting_complete_name,
ae_line_reference,
tax_code,
accounting_rule_name,
tax_exempt_number,
trx_line_type_name,
trx_line_number,
trx_detail_line_number,
trx_quantity,
sales_order_number,
salesrep_name,
tax_rate,
unit_selling_price,
trx_uom,
trx_source_name,
asset_number,
asset_description,
asset_book_type_code,
accounting_event_number,
accounting_event_type,
accounting_event_type_name,
accounting_line_number,
aeh_accounting_error_name,
ael_accounting_error_name,
user_je_category_name,
transfer_status_detail_name,
application_date,
applied_to_trx_hdr_currency,
applied_to_trx_hdr_date,
applied_to_trx_hdr_id,
applied_to_trx_hdr_number_c,
applied_to_trx_hdr_number_disp,
applied_to_trx_hdr_table,
applied_to_trx_line_number,
applied_to_trx_line_type,
applied_to_trx_line_type_name,
ar_activity_name,
ar_adjustment_creation_type,
ar_adjustment_type,
bank_account_name,
bank_statement_doc_seq_id,
bank_statement_doc_seq_name,
bank_statement_doc_seq_value,
bank_statement_line_number,
bank_statement_number,
bom_department_code,
bom_department_name,
chargeback_number,
cost_element_id,
cost_element_name,
distribution_set_name,
gl_batch_id,
inventory_item_locator_id,
inventory_item_locator_name,
inventory_item_revision,
item_description,
inventory_organization_code,
inventory_organization_id,
cost_type_id,
cost_type_name,
mfg_operation_seq_num,
payment_cleared_date,
payment_date,
payment_deposit_date,
payment_number,
payment_recon_currency,
po_line_num,
po_order_number,
po_order_release_num,
price_override,
po_order_shipment_num,
po_order_distribution_num,
po_order_type,
rcv_receipt_num,
rcv_shipment_header_id,
reversal_comments,
reversal_date,
subinventory,
trx_hdr_currency,
trx_reason_name,
trx_source_type_id,
trx_source_type_name,
unit_cost,
unit_price,
wip_assembly,
wip_basis,
wip_flow_schedule_number,
wip_job_id,
wip_job_name,
wip_line_code,
wip_line_id,
wip_resource_id,
wip_resource_name,
wip_resource_seq_num,
pa_employee_num,
pa_employee_name,
pa_supplier_num,
pa_supplier_name,
pa_project_num,
pa_project_name,
pa_task_num,
pa_task_name,
pa_ei_ev_org,
pa_nl_resource_org,
pa_nl_resource,
pa_ei_ev_type,
pa_event_num,
pa_quantity,
pa_uom,
pa_period_date,
pa_gl_transfer_date,
pa_customer_number,
pa_customer_name,
pa_agreement_num,
pa_accrue_thru_date,
pa_dri_number,
pa_trans_id,
pa_line_number,
prvdr_organization_name,
recvr_organization_name,
prvdr_ou_name,
recvr_ou_name,
cc_type_name,
cc_proc_method_name,
application_id,
asset_id,
asset_category_id,
asset_key_ccid,
code_combination_id,
inventory_item_id,
set_of_books_id,
org_id,
trx_class,
trx_type_n,
trx_type_c,
trx_number_n,
trx_number_c,
doc_sequence_id,
trx_hdr_table,
trx_hdr_id,
acct_line_type,
currency_conversion_type,
third_party_type,
third_party_id,
third_party_sub_id,
gl_transfer_status,
source_table,
source_id,
ael_table,
ael_id,
aeh_accounting_error_code,
ael_accounting_error_code,
je_category,
trx_line_type,
trx_source_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
pa_project_id,
pa_view_labor_cost
FROM xla_ap_inv_ael_gl_v xla_ael_gl_v
WHERE je_header_id = 1512
AND je_line_num = 2080
ORDER BY trx_hdr_id, accounting_event_number, accounting_line_number
从接收追溯到接收事务
SELECT row_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
note_to_receiver,
pll_note_to_receiver,
rma_reference,
transaction_id,
interface_transaction_id,
displayed_trans_type,
transaction_type,
transact_qty,
transact_uom,
transaction_date,
item_rev,
displayed_dest_type,
deliver_to_location,
deliver_to_person,
subinventory,
receipt_num,
displayed_source_type,
order_num,
po_release,
order_line,
po_shipment,
supplier,
country_of_origin,
supplier_site,
source_doc_qty,
source_doc_uom,
vendor_item_num,
vendor_lot_num,
packing_slip,
freight_carrier,
bill_of_lading,
reason,
hazard,
un_num,
wip_entity_id,
wip_line_id,
wip_operation_seq_num,
receipt_exception_flag,
receiver,
source_type,
destination_type_code,
item_id,
category_id,
ship_to_location_id,
locator_id,
supplier_id,
supplier_site_id,
shipment_header_id,
shipment_line_id,
req_header_id,
req_line_id,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
po_release_id,
shipment_num,
displayed_source_doc_code,
source_doc_code,
item_desc,
routing,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
currency_code,
currency_conversion_type,
currency_conversion_date,
currency_conversion_rate,
rate_type_display,
oe_order_header_id,
oe_order_num,
oe_order_line_id,
oe_order_line_num,
customer_id,
customer,
customer_site_id,
inspection_quality_code,
secondary_quantity,
secondary_unit_of_measure
FROM rcv_vrc_txs_v
WHERE EXISTS
(SELECT 1
FROM rcv_shipment_lines rsl
WHERE rsl.to_organization_id = 87
AND rcv_vrc_txs_v.shipment_header_id = rsl.shipment_header_id)
AND - 1 = -1
AND (rcv_vrc_txs_v.organization_id = 87)
AND (transaction_id = 10939)
ORDER BY transact_qty, displayed_trans_type DESC, transaction_date DESC
扫一扫
1593
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
