[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT last_run_date
INTO l_last_run_date
FROM csd_fact_details
WHERE fact_name = p_fact_name;
DELETE FROM CSD_FACT_DETAILS where fact_name = C_CSD_RETURNS_BI_RCV_TXNS_F;
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Delete successful.');
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting into CSD_RETURNS_BI_RCV_TXNS_F ...');
INSERT INTO CSD_RETURNS_BI_RCV_TXNS_F
(
transaction_id,
order_header_id,
order_line_id,
operating_unit_id,
inventory_org_id,
inventory_item_id,
received_quantity,
primary_uom_code,
transaction_date,
return_stream_code,
received_weight,
weight_uom_code,
received_value,
currency_code,
category_id,
category_set_id,
ship_geography_type,
ship_geography_id,
customer_type, -- swai: 12.2
pom_offset, -- swai: 12.2
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
SELECT distinct
rcv.transaction_id,
oh.header_id order_header_id,
oe.line_id order_line_id,
oe.org_id operating_unit_id,
rcv.organization_id inventory_org_id,
oe.inventory_item_id,
csd_returns_bi_util.convert_inv_uom( rcv.quantity, rcv.uom_code, mtl.primary_uom_code , 0 ) rcv_quantity, -- swai: bug 12614053
mtl.primary_uom_code,
trunc(rcv.transaction_date) transaction_date,
csd.return_stream_code,
(mtl.unit_weight*csd_returns_bi_util.convert_inv_uom( rcv.quantity, rcv.uom_code, mtl.primary_uom_code , null )) rcv_weight,
mtl.weight_uom_code,
(cic.item_cost* csd_returns_bi_util.convert_inv_uom( rcv.quantity, rcv.uom_code, mtl.primary_uom_code , null )) rcv_value,
gl.currency_code,
cat.category_id, -- swai: 12.2
cat.category_set_id, -- swai: 12.2
nvl(geo.geography_type,geo2.geography_type), -- swai: 12.2
nvl(geo.geography_id, geo2.geography_id), -- swai: 12.2
hp.party_type customer_type, -- swai: 12.2
csd.pom_offset, -- swai: 12.2
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
l_program_id,
l_program_login_id,
l_program_application_id,
l_request_id
FROM
oe_order_lines_all oe,
oe_order_headers_all oh ,
rcv_transactions rcv,
mtl_system_items_b mtl,
cst_item_costs cic,
gl_sets_of_books gl,
hr_operating_units hr,
csd_return_stream_reasons csd,
-- swai: 12.2 begin
mtl_item_categories cat,
hz_geo_name_references geo,
hz_cust_site_uses_all csu,
hz_cust_acct_sites_all cas,
hz_party_sites hps,
hz_geo_name_references geo2,
hz_cust_site_uses_all csu2,
hz_cust_acct_sites_all cas2,
hz_party_sites hps2,
hz_cust_accounts hca,
hz_parties hp
-- swai: 12.2 end
WHERE oe.header_id = oh.header_id
and rcv.oe_order_line_id = oe.line_id
and rcv.oe_order_header_id = oe.header_id
and rcv.transaction_type = 'DELIVER'
and rcv.source_document_code = 'RMA'
and mtl.inventory_item_id = oe.inventory_item_id
and mtl.organization_id = rcv.organization_id
and cic.inventory_item_id(+) = mtl.inventory_item_id
and cic.organization_id(+) = mtl.organization_id
and cic.cost_type_id(+) = 1
and hr.set_of_books_id = gl.set_of_books_id
and hr.organization_id = oe.org_id
and oe.return_reason_code = csd.return_reason_code
and rcv.transaction_date <= l_run_date
-- swai: 12.2 begin
and mtl.inventory_item_id = cat.inventory_item_id(+)
and mtl.organization_id = cat.ORGANIZATION_ID(+)
and oe.ship_to_org_id = csu.site_use_id(+)
and csu.cust_acct_site_id = cas. cust_acct_site_id(+)
and cas. party_site_id = hps.party_site_id(+)
and hps.location_id = geo.location_id(+)
and geo.location_table_name(+) = 'HZ_LOCATIONS'
and oh.sold_to_org_id = hca.cust_account_id(+)
and hca.party_id = hp.party_id(+)
-- next 4 joins for getting the order header ship-to info
-- in case order line info is not avail
and oh.ship_to_org_id = csu2.site_use_id(+)
and csu2.cust_acct_site_id = cas2.cust_acct_site_id(+)
and cas2. party_site_id = hps2.party_site_id(+)
and hps2.location_id = geo2.location_id(+)
and geo2.location_table_name(+) = 'HZ_LOCATIONS'
-- swai: 12.2 end
;
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting a record into CSD_FACT_DETAILS ...');
INSERT INTO CSD_FACT_DETAILS
(fact_name,
last_run_date,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id)
VALUES
(C_CSD_RETURNS_BI_RCV_TXNS_F,
l_run_date,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
l_program_id,
l_program_login_id,
l_program_application_id,
l_request_id);
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
DELETE FROM CSD_FACT_DETAILS where fact_name = C_CSD_RETURNS_BI_SHP_TXNS_F;
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Delete successful.');
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting into CSD_RETURNS_BI_SHP_TXNS_F ...');
INSERT INTO CSD_RETURNS_BI_SHP_TXNS_F
(
transaction_id,
order_header_id,
order_line_id,
operating_unit_id,
inventory_org_id,
inventory_item_id,
shipped_quantity,
primary_uom_code,
transaction_date,
shipped_weight,
weight_uom_code,
category_id,
category_set_id,
ship_geography_type,
ship_geography_id,
customer_type,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
SELECT distinct
shp.delivery_detail_id,
oh.header_id order_header_id,
oe.line_id order_line_id,
oe.org_id operating_unit_id,
shp.organization_id inventory_org_id,
oe.inventory_item_id,
nvl(csd_returns_bi_util.convert_inv_uom( shp.shipped_quantity, shp.requested_quantity_uom, mtl.primary_uom_code , null ), 0) shp_quantity, -- swai: bug 12614053
mtl.primary_uom_code,
trunc(oe.actual_shipment_date) transaction_date,
(mtl.unit_weight*csd_returns_bi_util.convert_inv_uom( shp.shipped_quantity, shp.requested_quantity_uom, mtl.primary_uom_code , null )) shp_weight,
mtl.weight_uom_code,
cat.category_id,
cat.category_set_id,
geo.geography_type,
geo.geography_id,
hp.party_type customer_type,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
l_program_id,
l_program_login_id,
l_program_application_id,
l_request_id
FROM
oe_order_lines_all oe,
oe_order_headers_all oh ,
wsh_delivery_details shp,
mtl_system_items_kfv mtl,
mtl_item_categories cat,
hz_geo_name_references geo,
hz_cust_site_uses_all csu,
hz_cust_acct_sites_all cas,
hz_party_sites hps,
hz_cust_accounts hca,
hz_parties hp
WHERE oe.header_id = oh.header_id
and shp.source_header_id = oe.header_id
and shp.source_line_id = oe.line_id
and shp.source_code = 'OE'
and shp.released_status in ('C','I')
and mtl.inventory_item_id = oe.inventory_item_id
and mtl.organization_id = shp.organization_id
and oe.actual_shipment_date <= l_run_date
and mtl.inventory_item_id = cat.inventory_item_id(+)
and mtl.organization_id = cat.organization_id(+)
and oe.ship_to_org_id = csu.site_use_id(+)
and csu.cust_acct_site_id = cas. cust_acct_site_id(+)
and cas. party_site_id = hps.party_site_id(+)
and hps.location_id = geo.location_id(+)
and geo.location_table_name(+) = 'HZ_LOCATIONS'
and oh.sold_to_org_id = hca.cust_account_id(+)
and hca.party_id = hp.party_id(+);
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting a record into CSD_FACT_DETAILS ...');
INSERT INTO CSD_FACT_DETAILS
(fact_name,
last_run_date,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id)
VALUES
(C_CSD_RETURNS_BI_SHP_TXNS_F,
l_run_date,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
l_program_id,
l_program_login_id,
l_program_application_id,
l_request_id);
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
DELETE FROM CSD_FACT_DETAILS where fact_name = C_CSD_RETURNS_BI_MTL_TXNS_F;
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Delete successful.');
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting into CSD_RETURNS_BI_MTL_TXNS_F ...');
INSERT INTO CSD_RETURNS_BI_MTL_TXNS_F
(transaction_id,
operating_unit_id,
inventory_org_id,
inventory_item_id,
transacted_qty,
primary_uom_code,
transaction_date,
reason_id,
mtl_disposition_code,
transacted_weight,
weight_uom_code,
category_id,
category_set_id,
inv_geography_type,
inv_geography_id,
recovery_value, -- swai: 12.2
processing_cost, -- swai: 12.2
currency_code, -- swai: 12.2
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
SELECT
txn.transaction_id,
orgv.operating_unit operating_unit_id,
txn.organization_id inventory_org_id,
txn.inventory_item_id,
csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , 0 ) transacted_quantity, -- swai: bug 12614053
mtl.primary_uom_code,
trunc(txn.transaction_date) transaction_date,
txn.reason_id,
dis.mtl_disposition_code,
(mtl.unit_weight*csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , null )) transacted_weight,
mtl.weight_uom_code,
cat.category_id, -- swai: 12.2
cat.category_set_id, -- swai: 12.2
geo.geography_type, -- swai: 12.2
geo.geography_id, -- swai: 12.2
(cic.item_cost*csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , null )) recovery_value, -- swai: 12.2
(mtl.order_cost*csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , null )) processing_cost, -- swai: 12.2
gl.currency_code, -- swai: 12.2
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
l_program_id,
l_program_login_id,
l_program_application_id,
l_request_id
FROM
mtl_material_transactions txn,
mtl_system_items_b mtl,
csd_mtl_disposition_reasons dis,
org_organization_definitions orgv,
-- swai: 12.2 begin
mtl_item_categories cat,
hz_geo_name_references geo,
mtl_secondary_inventories subinv,
gl_sets_of_books gl,
hr_operating_units hr,
cst_item_costs cic
-- swai: 12.2 end
WHERE txn.inventory_item_id = mtl.inventory_item_id
and txn.organization_id = mtl.organization_id
and txn.transaction_type_id = 43
and txn.reason_id = dis.mtl_txn_reason_id
and txn.organization_id = orgv.organization_id
and txn.transaction_date <= l_run_date
-- swai: 12.2 begin
and mtl.inventory_item_id = cat.inventory_item_id(+)
and mtl.organization_id = cat.organization_id(+)
and txn.subinventory_code = subinv.secondary_inventory_name(+)
and txn.organization_id = subinv.organization_id(+)
and geo.location_id(+) = subinv.location_id
and geo.location_table_name(+) = 'HR_LOCATIONS_ALL'
and hr.set_of_books_id = gl.set_of_books_id
and hr.set_of_books_id = gl.set_of_books_id
and hr.organization_id = orgv.operating_unit
and cic.inventory_item_id(+) = mtl.inventory_item_id
and cic.organization_id(+) = mtl.organization_id
and cic.cost_type_id(+) = 1
-- swai: 12.2 end
UNION ALL
SELECT
txn.transaction_id,
orgv.operating_unit operating_unit_id,
txn.organization_id inventory_org_id,
txn.inventory_item_id,
csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , 0 ) txn_quantity, -- swai: bug 12614053
mtl.primary_uom_code,
trunc(txn.transaction_date) transaction_date,
txn.reason_id,
dis.mtl_disposition_code,
(mtl.unit_weight*csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , null )) transacted_weight,
mtl.weight_uom_code,
cat.category_id, -- swai: 12.2
cat.category_set_id, -- swai: 12.2
geo.geography_type, -- swai: 12.2
geo.geography_id, -- swai: 12.2
(cic.item_cost*csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , null )) recovery_value, -- swai: 12.2
(mtl.order_cost*csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , null )) processing_cost, -- swai: 12.2
gl.currency_code, -- swai: 12.2
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
l_program_id,
l_program_login_id,
l_program_application_id,
l_request_id
FROM
mtl_material_transactions txn,
mtl_system_items_b mtl,
csd_mtl_disposition_reasons dis,
org_organization_definitions orgv,
-- swai: 12.2 begin
mtl_item_categories cat,
hz_geo_name_references geo,
mtl_secondary_inventories subinv,
gl_sets_of_books gl,
hr_operating_units hr,
cst_item_costs cic
-- swai: 12.2 end
WHERE txn.inventory_item_id = mtl.inventory_item_id
and txn.organization_id = mtl.organization_id
and txn.transaction_type_id = 38
and txn.reason_id = dis.mtl_txn_reason_id
and txn.organization_id = orgv.organization_id
and txn.transaction_date <= l_run_date
-- swai: 12.2 begin
and mtl.inventory_item_id = cat.inventory_item_id(+)
and mtl.organization_id = cat.organization_id(+)
and txn.subinventory_code = subinv.secondary_inventory_name(+)
and txn.organization_id = subinv.organization_id(+)
and geo.location_id(+) = subinv.location_id
and geo.location_table_name(+) = 'HR_LOCATIONS_ALL'
and hr.set_of_books_id = gl.set_of_books_id
and hr.set_of_books_id = gl.set_of_books_id
and hr.organization_id = orgv.operating_unit
and cic.inventory_item_id(+) = mtl.inventory_item_id
and cic.organization_id(+) = mtl.organization_id
and cic.cost_type_id(+) = 1;
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting a record into CSD_FACT_DETAILS ...');
INSERT INTO CSD_FACT_DETAILS
(fact_name,
last_run_date,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id)
VALUES
(C_CSD_RETURNS_BI_MTL_TXNS_F,
l_run_date,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
l_program_id,
l_program_login_id,
l_program_application_id,
l_request_id);
FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');